Question on automating Prod restores to Test

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Question on automating Prod restores to Test

Author

Topic

Wanabe
Starting Member

7 Posts

Posted - 2009-03-06 : 15:35:04

Hello,

I'm pretty new to SQL and T-SQL in general but I understand the basics.

I'm trying to automate the restore of our Prod DB backup into our Test environment and have a question.

I found this simple SQL script online that looks pretty promising:

ALTER DATABASE Northwind SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE Northwind
FROM DISK = 'c:\Northwind.BAK'
WITH MOVE 'Northwind_Data' TO 'c:\data\Northwind.mdf',
MOVE 'Northwind _Log' TO 'c:\data\Northwind _log.ldf'

ALTER DATABASE Northwind SET MULTI_USER

EXEC sp_change_users_login 'Update_One', 'myuser', 'myuser'

It's a simple restore but I think it's enough for me to work off of and build from there. We have our own script that we run for SQL users (sp_change_user_login).

My question is:

1) How could this be modified to copy the most recent backup of our Prod DB. The backup filename changes for each days backup (db_200903050600.BAK, db_200903050601.BAK, etc.).

Thanks!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-03-06 : 17:05:45

If you have xp_cmdshell available, something like this will work.


if object_id('tempdb..#dir','U') is not null begin drop table #dir end

create table #dir ( cmd_out varchar(2000) null)

insert into #dir
exec master.dbo.xp_cmdshell
'dir \\servername\sharename\backup_dir_name\*.bak /b'

declare @backup_file varchar(2000)

select @backup_file = rtrim(max(cmd_out)) from #dir where cmd_out is not null

select @backup_file

CODO ERGO SUM

Wanabe
Starting Member

7 Posts

Posted - 2009-03-06 : 17:12:55

Thanks, MIchael. Being that I'm not as advanced as you can you would it be possible for you to provide a detail of exactly what this script is doing...step by step?

Thanks!

Wanabe
Starting Member

7 Posts

Posted - 2009-03-06 : 20:27:15

Scratch that last question. I can see what's happening here. I guess my next question would be...now that the most recent backup file has been identified, how do I write my RESTORE statement to use that value for the filename to backup?

Thanks!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-06 : 20:30:42

[code]RESTORE DATABASE Northwind
FROM DISK = @backup_file
WITH MOVE 'Northwind_Data' TO 'c:\data\Northwind.mdf',
MOVE 'Northwind _Log' TO 'c:\data\Northwind _log.ldf' [/code]

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-03-06 : 20:31:56

[code]
declare @backup_path_and_filename varchar(2000)

set @backup_path_and_filename = '\\servername\sharename\backup_dir_name\'+@backup_file

RESTORE DATABASE MyDatabase
FROM DISK = @backup_path_and_filename
... and so on...
[/code]

CODO ERGO SUM

Wanabe
Starting Member

7 Posts

Posted - 2009-03-06 : 20:33:10

Brilliant! Thanks to you both.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources