Creating a copy of a database on a different server

Question

Creating a copy of a database on a different server

Walshy

Mr or Mrs. 500

Points: 590
More actions
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

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

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

Ant-Green SSC Guru Points: 113509 More actions · Answer 1

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

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 2

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.

Jeffrey Williams SSC Guru Points: 90314 More actions · Answer 3

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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