World Search

Thursday, June 19, 2014

Limiting No Of Rows and Sizing the bufferon SSIS




Adding Columns to Replicated Tables


sp_repladdcolumn @source_object =
  'authors'
   , @column =  'newcol'
   , @typetext = 'INT'
   , @publication_to_add = '       of publication authors is
      included in>'


Moving Database Files on the secondary server in Log Shipping

http://blogs.technet.com/b/mdegre/archive/2009/09/25/moving-database-files-on-the-secondary-server-in-log-shipping.aspx

 

 

You are using Microsoft SQL Server Logshipping for one of your VLDB (very large database). You would like to move data files on another disk on the secondary server without reconfiguring log shipping.

As you cannot use the detach/attach database, you could follow the way below :

This solution is inspired of the documentation "Moving Database Files" http://technet.microsoft.com/en-us/library/ms345483.aspx


Step 1 : If your secondary is with the option "Standby Mode", you must change by "no recovery mode". And perform a transaction (insert, delete or update)
After, you have to manually run the job of backup, Then the jog of copy and the restore job.

Step 2 : Collect the logical name :

SELECT
  name as logicalname, physical_name as filename
FROM
  sys.master_files
WHERE
  database_id = DB_ID('yourDatabaseName');

Step 3 : Move the file on the path expected :

ALTER DATABASE yourDatabaseName
MODIFY FILE( NAME = logicalname, FILENAME = 'M:\newpath.mdf')

Step 4 : Stop SQL server service, move the database file and start SQL server Service

Step 5 : Check that the update is ok with the query below :

SELECT
  name as logicalname, physical_name as filename
FROM
  sys.master_files
WHERE
  database_id = DB_ID('yourDatabaseName');


You can follow the same way to add a file to a log shipped database.