April 26, 2017 at 3:46 pm
Is there a simple and clean script that can copy a full backup of a database and restore the database to a test server?
The database is under 5 GB, I prefer copy to test local drive then do the restore.
Also if the script can include a backup of the database on production at first step, it will be good too.
Thanks,
Anne
April 27, 2017 at 4:10 am
Create an SSIS package that does this for you or a SQL job which fires over linked servers.
SSIS would be my preferred.
If you have a shared UNC path easy enough to do in two steps
Backup source to UNC, restore destination from UNC
If not, backup source to local drive, file copy task to copy to test, restore database
April 27, 2017 at 4:55 am
Hi,
if you would use powershell, there are some usefull scripts:
https://dbatools.io/
Kind regards,
Andreas
April 27, 2017 at 7:50 am
sqlfriends - Wednesday, April 26, 2017 3:46 PMIs there a simple and clean script that can copy a full backup of a database and restore the database to a test server?The database is under 5 GB, I prefer copy to test local drive then do the restore.
Also if the script can include a backup of the database on production at first step, it will be good too.Thanks,
Anne
If the servers are on the same domain, setup a directory "share" at the Windows level and just do a restore from the latest prod backup. No need for file movement.
You should be doing something like this all the time, anyway, to guarantee that your backups are good. As the saying goes, "You don't need a backup plan... You need a RESTORE plan.".
--Jeff Moden
Change is inevitable... Change for the better is not.
April 27, 2017 at 10:34 am
Jeff Moden - Thursday, April 27, 2017 7:50 AMsqlfriends - Wednesday, April 26, 2017 3:46 PMIs there a simple and clean script that can copy a full backup of a database and restore the database to a test server?The database is under 5 GB, I prefer copy to test local drive then do the restore.
Also if the script can include a backup of the database on production at first step, it will be good too.Thanks,
AnneIf the servers are on the same domain, setup a directory "share" at the Windows level and just do a restore from the latest prod backup. No need for file movement.
You should be doing something like this all the time, anyway, to guarantee that your backups are good. As the saying goes, "You don't need a backup plan... You need a RESTORE plan.".
Yes, it is on same domain.
But if just use a mapped drive, the restore process will go through share on the remote server, is it more slow and less effective than copying it to local to do the restore?
Thanks,
April 27, 2017 at 10:55 am
sqlfriends - Thursday, April 27, 2017 10:34 AMJeff Moden - Thursday, April 27, 2017 7:50 AMsqlfriends - Wednesday, April 26, 2017 3:46 PMIs there a simple and clean script that can copy a full backup of a database and restore the database to a test server?The database is under 5 GB, I prefer copy to test local drive then do the restore.
Also if the script can include a backup of the database on production at first step, it will be good too.Thanks,
AnneIf the servers are on the same domain, setup a directory "share" at the Windows level and just do a restore from the latest prod backup. No need for file movement.
You should be doing something like this all the time, anyway, to guarantee that your backups are good. As the saying goes, "You don't need a backup plan... You need a RESTORE plan.".
Yes, it is on same domain.
But if just use a mapped drive, the restore process will go through share on the remote server, is it more slow and less effective than copying it to local to do the restore?Thanks,
How long does copying the backup file take? It's likely going to be faster to restore directly from the backup location as opposed to copying the file and then restoring.
Does the service account on the destination server have access to the backup location? Make sure that you have granted the proper permissions if you are going to restore directly from the backup.
If you want an easy script, right click on the database, pick tasks, pick restore, and pick database.
Enter all of the values as you want, and then generate the scripts.
And, like Jeff said, this should be part of you regular routine.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 27, 2017 at 11:48 am
It takes about 5 minutes to copy the file for this case.
But could be any minutes depending on the size of the database.
I asked is because the restore if remotely, I think it is less effective and slow comparing with doing it locally
April 27, 2017 at 12:33 pm
sqlfriends - Thursday, April 27, 2017 11:48 AMIt takes about 5 minutes to copy the file for this case.
But could be any minutes depending on the size of the database.I asked is because the restore if remotely, I think it is less effective and slow comparing with doing it locally
"Less effective"??? Unless you have some network issues that cause problems, I'm not sure that's an accurate statement.
And certainly the actual restore will be faster from a local drive as opposed to across the network, but the time it takes to copy the file should make it slower.
We have a process that restores just less than 1 TB of production databases on a nightly basis. The slowest restore of a 200 GB database takes about 35-45 minutes, the entire process completes in 40-55 minutes.
Previously it took 4+ hours because the process copied the backups first, and did the restores sequentially.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 27, 2017 at 2:23 pm
Here's a script that I actually use:
It creates a job on Development, which does an on-demand copy_only backup of prod, copies it to the right spot on Dev via powershell, then restores.
I use this when someone needs the database refreshed.
it is using a proxy user named SQLAdmin, you will have to change that.
because of the MOVE commands for the files, you have to change some parts that are related to the mdf/ldf/ndf files, but once set up, you should be good to go.
let me know if this helps at all.--#################################################################################################
-- Real World DBA Toolkit version 4.94 Lowell Izaguirre lowell@stormrage.com
--#################################################################################################USE [msdb];
USE [msdb];
GO
/****** Object: Job [Database Maintenance: Restore Production SandBox To Dev] Script Date: 2/17/2017 7:44:33 AM ******/
BEGIN TRANSACTION;
DECLARE @ReturnCode INT;
SELECT @ReturnCode = 0;
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 2/17/2017 7:44:33 AM ******/
IF NOT EXISTS ( SELECT [syscategories].[name]
FROM [msdb].[dbo].[syscategories]
WHERE [syscategories].[name] = N'[Uncategorized (Local)]'
AND [syscategories].[category_class] = 1 )
BEGIN
EXEC @ReturnCode = [msdb].[dbo].[sp_add_category] @class = N'JOB', @type = N'LOCAL', @name = N'[Uncategorized (Local)]';
IF ( @@ERROR <> 0
OR @ReturnCode <> 0
)
GOTO QuitWithRollback;
END;
DECLARE @jobId BINARY(16);
EXEC @ReturnCode = [msdb].[dbo].[sp_add_job] @job_name = N'Database Maintenance: Restore Production SandBox To Dev', @enabled = 1, @notify_level_eventlog = 0,
@notify_level_email = 2, @notify_level_netsend = 0, @notify_level_page = 0, @delete_level = 0, @description = N'No description available.',
@category_name = N'[Uncategorized (Local)]', @owner_login_name = N'mydomain\lizaguirre', @notify_email_operator_name = N'DBA Team', @job_id = @jobId OUTPUT;
IF ( @@ERROR <> 0
OR @ReturnCode <> 0
)
GOTO QuitWithRollback;
/****** Object: Step [Backup OurServerSSEG.SandBox As SandBoxSnapshot.bak] Script Date: 2/17/2017 7:44:35 AM ******/
EXEC @ReturnCode = [msdb].[dbo].[sp_add_jobstep] @job_id = @jobId, @step_name = N'Backup OurServerSSEG.SandBox As SandBoxSnapshot.bak', @step_id = 1,
@cmdexec_success_code = 0, @on_success_action = 3, @on_success_step_id = 0, @on_fail_action = 2, @on_fail_step_id = 0, @retry_attempts = 0,
@retry_interval = 0, @os_run_priority = 0, @subsystem = N'TSQL', @command = N'EXECUTE(''
BACKUP DATABASE [SandBox]
TO DISK = N''''E:\MSSQL\Backups\OurServerSSEG_SandBox_Snapshot.bak''''
WITH COPY_ONLY,
NOFORMAT,
INIT,
NAME = N''''SandBox-Full Database Backup'''',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10'') AT OurServerSSEG
', @database_name = N'master', @flags = 0;
IF ( @@ERROR <> 0
OR @ReturnCode <> 0
)
GOTO QuitWithRollback;
/****** Object: Step [Copy SandBoxSnapshot.bak to local drive] Script Date: 2/17/2017 7:44:36 AM ******/
EXEC @ReturnCode = [msdb].[dbo].[sp_add_jobstep] @job_id = @jobId, @step_name = N'Copy SandBoxSnapshot.bak to local drive', @step_id = 2, @cmdexec_success_code = 0,
@on_success_action = 3, @on_success_step_id = 0, @on_fail_action = 2, @on_fail_step_id = 0, @retry_attempts = 0, @retry_interval = 0, @os_run_priority = 0,
@subsystem = N'CmdExec', @command = N'ROBOCOPY "\\OurServerSSEG\E$\MSSQL\Backups" "\\OurServerDev\F$\Backup\SQL" "OurServerSSEG_SandBox_Snapshot.bak"
IF %ERRORLEVEL% LSS 8 EXIT /B 0
EXIT /B 1', @flags = 0;
IF ( @@ERROR <> 0
OR @ReturnCode <> 0
)
GOTO QuitWithRollback;
/****** Object: Step [Resxtore SandBoxSnapshot On Dev] Script Date: 2/17/2017 7:44:37 AM ******/
EXEC @ReturnCode = [msdb].[dbo].[sp_add_jobstep] @job_id = @jobId, @step_name = N'Resxtore SandBoxSnapshot On Dev', @step_id = 3, @cmdexec_success_code = 0,
@on_success_action = 3, @on_success_step_id = 0, @on_fail_action = 2, @on_fail_step_id = 0, @retry_attempts = 0, @retry_interval = 0, @os_run_priority = 0,
@subsystem = N'TSQL', @command = N'ALTER DATABASE [SandBox] SET OFFLINE WITH ROLLBACK IMMEDIATE
USE [master]
RESTORE DATABASE [SandBox]
FROM DISK = N''F:\Backup\SQL\OurServerSSEG_SandBox_Snapshot.bak''
WITH FILE = 1,
MOVE N''SandBox'' TO N''G:\Data\SandBox.mdf'',
MOVE N''SandBoxReadOnly'' TO N''G:\Data\SandBoxReadOnly.ndf'',
MOVE N''SandBox_log'' TO N''F:\Log\SQL\SandBox_log.ldf'',
NOUNLOAD,
REPLACE,
STATS = 5;
--this specific database is typically kept in a READ_ONLY state. On Dev it must be READ_WRITE
ALTER DATABASE [SandBox] SET READ_WRITE', @database_name = N'master', @flags = 0;
IF ( @@ERROR <> 0
OR @ReturnCode <> 0
)
GOTO QuitWithRollback;
/****** Object: Step [Delete the huge backup off of OurServerSSEG] Script Date: 2/17/2017 7:44:37 AM ******/
EXEC @ReturnCode = [msdb].[dbo].[sp_add_jobstep] @job_id = @jobId, @step_name = N'Delete the huge backup off of OurServerSSEG', @step_id = 4, @cmdexec_success_code = 0,
@on_success_action = 3, @on_success_step_id = 0, @on_fail_action = 2, @on_fail_step_id = 0, @retry_attempts = 0, @retry_interval = 0, @os_run_priority = 0,
@subsystem = N'CmdExec', @command = N'DEL \\OurServerSSEG\e$\MSSQL\Backups\OurServerSSEG_SandBox_Snapshot.bak', @flags = 0;
IF ( @@ERROR <> 0
OR @ReturnCode <> 0
)
GOTO QuitWithRollback;
/****** Object: Step [Delete the huge backup off of OurServerDev] Script Date: 2/17/2017 7:44:37 AM ******/
EXEC @ReturnCode = [msdb].[dbo].[sp_add_jobstep] @job_id = @jobId, @step_name = N'Delete the huge backup off of OurServerDev', @step_id = 5, @cmdexec_success_code = 0,
@on_success_action = 3, @on_success_step_id = 0, @on_fail_action = 2, @on_fail_step_id = 0, @retry_attempts = 0, @retry_interval = 0, @os_run_priority = 0,
@subsystem = N'CmdExec', @command = N'DEL \\OurServerDev\F$\Backup\SQL\OurServerSSEG_SandBox_Snapshot.bak', @flags = 0;
IF ( @@ERROR <> 0
OR @ReturnCode <> 0
)
GOTO QuitWithRollback;
/****** Object: Step [Notify BI Team that the database was refreshed] Script Date: 2/17/2017 7:44:37 AM ******/
EXEC @ReturnCode = [msdb].[dbo].[sp_add_jobstep] @job_id = @jobId, @step_name = N'Notify BI Team that the database was refreshed', @step_id = 6,
@cmdexec_success_code = 0, @on_success_action = 1, @on_success_step_id = 0, @on_fail_action = 2, @on_fail_step_id = 0, @retry_attempts = 0,
@retry_interval = 0, @os_run_priority = 0, @subsystem = N'TSQL', @command = N'EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''SQLAdmin'',
@recipients = ''lizaguirre@somedomain.org;'',
@subject = ''OurServerDev SandBox Database Restored From Production'',
@body = ''{from Lowell}<br /> The database SandBox from production has been backed up and restored from Production with todays most current data.'' ,
@body_format = ''HTML''
', @database_name = N'master', @flags = 0;
IF ( @@ERROR <> 0
OR @ReturnCode <> 0
)
GOTO QuitWithRollback;
EXEC @ReturnCode = [msdb].[dbo].[sp_update_job] @job_id = @jobId, @start_step_id = 1;
IF ( @@ERROR <> 0
OR @ReturnCode <> 0
)
GOTO QuitWithRollback;
EXEC @ReturnCode = [msdb].[dbo].[sp_add_jobserver] @job_id = @jobId, @server_name = N'(local)';
IF ( @@ERROR <> 0
OR @ReturnCode <> 0
)
GOTO QuitWithRollback;
COMMIT TRANSACTION;
GOTO EndSave;
QuitWithRollback:
IF ( @@TRANCOUNT > 0 )
ROLLBACK TRANSACTION;
EndSave:
GO
Lowell
April 27, 2017 at 2:29 pm
Thanks, I will give it a try.
April 27, 2017 at 2:32 pm
correction: the above uses a simpler cmdexec instead of powershell to call robocopy or the DEL command. same thing, different island.
Lowell
April 27, 2017 at 2:37 pm
I would like to include the script of it to a build process instead of a SQL job.
Is there a way that I can have the backup- copy-restore inside a sqlcmd sql file not including the code for job steps?
Thanks,
April 27, 2017 at 3:36 pm
sqlfriends - Thursday, April 27, 2017 10:34 AMJeff Moden - Thursday, April 27, 2017 7:50 AMsqlfriends - Wednesday, April 26, 2017 3:46 PMIs there a simple and clean script that can copy a full backup of a database and restore the database to a test server?The database is under 5 GB, I prefer copy to test local drive then do the restore.
Also if the script can include a backup of the database on production at first step, it will be good too.Thanks,
AnneIf the servers are on the same domain, setup a directory "share" at the Windows level and just do a restore from the latest prod backup. No need for file movement.
You should be doing something like this all the time, anyway, to guarantee that your backups are good. As the saying goes, "You don't need a backup plan... You need a RESTORE plan.".
Yes, it is on same domain.
But if just use a mapped drive, the restore process will go through share on the remote server, is it more slow and less effective than copying it to local to do the restore?Thanks,
It certainly can be more slow especially if the backups are stored "off SAN" (and they ABSOLUTELY should be!). When I say that, "more slow" means the total time the database might take to recover. The overall time of the batch won't change by much because the data still has to be copied one way or the other. Restoring from a local copy removes the over-the-network time.
The problem that many people have with doing the copy-then-restore method is the copy part. In their effort to avoid things like xp_CmdShell or CmdExec, they end up building something a fair bit more complex than just issuing a simple COPY command at the OS level and then doing a simple RESTORE.
Provided that you take the time to properly understand what's happening and to modify it for your own environment, Lowell's code looks great if you want to do the Copy-then-Restore method.
If you do have a SAN, you might want to check if the SAN has the ability to do "SAN snapshots" (for lack of a better term). They're nearly instantaneous and are auto-magically up to date without having to take a backup to get to the recent point in time. We dp similar on our PreProd box. The whole 2TB box takes less than a minute. I have worked on some boxes in the past where it takes only seconds (I forget the type of SAN it was but it was amazing even back in 2004).
--Jeff Moden
Change is inevitable... Change for the better is not.
April 27, 2017 at 4:07 pm
Thanks Jeff for the detailed explanation, that is what I am looking for and made a lot of sense.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply