March 25, 2014 at 12:18 am
Hi All.
I have two servers on different machines:
Production & Testing
I have a database called MyData on Production.
I can link the servers using sp_AddLinkedServer.
I want to make a copy of MyData from Production to Testing and rename it from MyData to MyData_Testing.
Currently, I figure I will have to make a backup - copy it from Production to Testing - restore it. Since I can link the servers, I can initiate the backup from Testing - copy - and restore from a script on Testing. The only part I am not sure of is how to copy the file via the script.
Any better ideas or help on how to copy the file?
Thanks!
Mike
March 25, 2014 at 1:22 am
Do not use linkedserver for copy database. It will be security threat as users having access to test can access data on prod. And what if they think deleting in Test but delet record of prod?
The best option, there must be backup plan on prod? If yes, you can copy backup file from one location (prod) to other (test) based on database size(What is db backup size?). You can use copy command in job to automate the process. Once copy is complete you can restore via job. You can use ROBOCOPY to copy file from one location to other location.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 25, 2014 at 4:26 am
Don't bother with linked servers.
You can make a full backup with copy only (this ensures it doesn't break your backup chain on the primary server), move the backup file to the new server, then restore it as a new DB. This means you can keep the primary DB online the whole time.
Or if you want a simpler method, you can bring the primary DB offline, copy the .mdf and .ldf files to the new server, bring the primary back online, and mount the .mdf and .ldf files on the second server as a new DB, this may seem more straightforward but it means you have downtime on the primary server.
I'd suggest using the first method to keep your primary server online. Use the following script to make the backup, maintaining the backup chain (edit these scripts as required, inserting your own DB name and folder locations):
USE [YourDatabaseNameGoesHere]
BACKUP DATABASE [YourDatabaseNameGoesHere]
TO DISK = N'f:\Temp\DBs\[YourDatabaseNameGoesHere].bak'
WITH COPY_ONLY
Or if you want to move all your DBs, use this:
EXEC sp_MSForEachDB '
IF ''?'' IN (''master'', ''msdb'',''model'',''tempdb'',''AffiliateSystem_temp'',''ClubWorldGroup'',''AEDW_Testing'',''Sandbox'')
BEGIN
USE ?
BACKUP DATABASE ?
TO DISK = N''f:\Temp\DBs\?.bak''
WITH COPY_ONLY
--PRINT ''?''
END
'
You can then use xp_cmdshell if you want to move the backup files from SSMS:
xp_cmdshell 'copy f:\Temp\DBs\[YourDatabaseNameGoesHere].bak YourDestinationGoesHere.bak'
As you can see this just opens a command shell and does whatever is in the ' and ', so you can copy or move the files as you see fit here.
Then you just need to connect to the secondary server where you want the DB(s) restored to and run the following:
RESTORE DATABASE [YourDatabaseNameGoesHere]
FROM DISK = N'c:\backups\backup1.bak'
WITH RECOVERY
March 25, 2014 at 12:34 pm
Unless you have a problem with the network between those servers - it could be much easier to just restore across the network.
RESTORE DATABASE {...}
FROM FILE = '\\prodserver\backupshare\backup_file.bak'
WITH ...
No need to copy the file across, then perform the restore...
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