Post reply

Creating a copy of a database on a different server

  • May 25, 2012 at 6:38 am

    #392349

    Hi,

    I need to create a copy of a database on a different server so it can be used for reporting purposes. This copy only needs to be made once a night. Both servers are SQL 2008 R2.

    What are the best options for carrying this out? I was thinking about using replication but my boss has told me not to use it.

    Thanks in advance.

    Andrew

  • May 25, 2012 at 6:46 am

    #1493118

    Number of options what could do this are Replication, Log Shipping, SSIS, Custom backup / restore automation

    All depends what you need to do. If its only a sub set of the tables, you might be best with doing a SSIS package which loads the data nightly, if its all tables look at LS or backup restore. They only problem with log shipping is depending how often you restore, will depend how often your users are disconnected, so will require some sort of user teaching to say, for example, every hour for 5 minutes on the hour, reports are offline due to a data refresh.

    Could if you have Enterprise edition look at CDC with SSIS to do incremental loads http://msdn.microsoft.com/en-us/library/bb895315.aspx

  • May 25, 2012 at 8:39 am

    #1493205

    I've scripted out a solution for this before.

    Here's a more generic version. I've written a VBScript ten-liner to append the date and time to the .bak file but there's a few ways you can do this and I won't bog down this post with the code. You could probably just pass in something from GETDATE() in SQL into the filename string, come to think about it.

    SEND.BAT

    SQLCMD -SserverA -dMyDB -E -iBackupCommand.sql > backupLog.log

    CD path

    COPY *.BAK Y:\path\.

    ERASE *.BAK

    BackupCommand.sql

    BACKUP DATABASE myDB TO DISK=N'X:\path\myDB_BACKUP_DATE_TIME_INITIALS.BAK'

    Then on Server B:

    RECEIVE.BAT

    SQLCMD -SserverB -E -iRestoreCommand.sql > restorelog.log

    ERASE *.BAK

    RestoreCommand.sql

    RESTORE DATABASE myDB FROM DISK=N'y:\path\myDB_BACKUP_DATE_TIME_INITIALS.BAK'

    WITH MOVE 'logicalFileName' TO 'Y:\path\filename.mdf',

    MOVE 'logicalFileName' TO 'Y:\path\filename2.ldf',

    -- Add more here if required.

    RECOVERY, REPLACE

    DBCC CHECKDB myDB

    Obviously swap out the parameters you need to, like your file paths and names, and put the batch files in Task Scheduler. You will also need the .sql files handy (same directory). Ensure they run as a Windows account that has the db_owner and db_backupadmin roles as a minimum or preferably the server role sysadmin.

    EDIT: Added REPLACE.

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • May 26, 2012 at 9:31 am

    #1493516

    There are many scripts available that will build your restore scripts for you from MSDB. I took one of those, tweaked it for my process and use that process.

    For my process, here is the basic outline:

    1) Split backup volume on source system

    2) Mount split volume to destination system

    3) Execute script to build restore script from source system - use SQLCMD to output script file to folder on destination system.

    4) Execute restore scripts on destination system

    5) Unmount volume on destination system

    6) Synchronize volume on source system

    I worked with the SAN team to setup the backup volume so it could be split and presented to the reporting system. This is required because the backup file is 300GB (compressed with Litespeed) and copying that amount of data across the network would cause issues.

    This whole process takes about 1 hour to restore multiple databases with transaction logs at a total size over 3TB allocated and about 1.8TB used.

    If you have a very fast and reliable backup network, with shared storage available you could skip a lot of steps and just backup to the network location and restore from that same network location. Just be sure you have a reliable and fast network between that storage and your SQL Server instances.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply