Automated Restore Procedue - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Automated Restore Procedue

Author

Topic

nguyenl
Posting Yak Master

128 Posts

Posted - 2007-11-28 : 17:50:53

Every Month I have to restore Database Northwind (Using this as an example) into Database Northwindtest. I Am trying to automate this process using a Job Agent. My Code is:

RESTORE DATABASE Northwindtest
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Gold_db_200711120300.bak'

The problem is I do a back up of this Database for day for a week. So I have:

Gold_db_200711120300.bak
Gold_db_200711130300.bak
Gold_db_200711140300.bak
Gold_db_200711150300.bak
Gold_db_200711160300.bak
Gold_db_200711170300.bak
Gold_db_200711180300.bak

How would I tell the Job agent to restore the most Current Full backup?

Thanks,

-Long

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-28 : 18:11:57

create table #t (dir varchar(2000))

insert into #t exec master.dbo.xp_cmdshell 'dir X:\Backup_Directory\MyDB_db_*.bak /b'

declare @filename varchar(500)

select @filename = max(dir) from #t where dir like 'MyDB_db_%.bak'

select backup_file= @filename

drop table #t

CODO ERGO SUM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-28 : 18:14:58

Here's some old code I wrote to do it (sorry about the bad blog formatting, I was new to the whole thing back then):
http://weblogs.sqlteam.com/tarad/archive/2005/11/08/8262.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-29 : 01:55:24

Also refer http://www.nigelrivett.net/SQLAdmin/s_RestoreLatestBackup.html

Madhivanan

Failing to plan is Planning to fail

nguyenl
Posting Yak Master

128 Posts

Posted - 2007-11-29 : 11:46:38

Thanks, for all of your help...I'm using this code here but when I open the table that was created it keeps saying "The system cannot find the path specified".

I am using this code here (Mentioned from Michael above):

create table Refresh (dir varchar(2000))

insert into Refresh exec master.dbo.xp_cmdshell 'dir C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Northwind_db_*.bak'

declare @filename varchar(500)

select @filename = max(dir) from Refresh where dir like 'Northwind_db_%.bak'

select backup_file= @filename

If I ever get the above code to work.....do I just add to more lines to do the restore so basically:

RESTORE DATABASE Northwindtest
FROM DISK = @filename

Thanks

-Long

nguyenl
Posting Yak Master

128 Posts

Posted - 2007-11-29 : 12:42:04

Sorry I also have to mention that I am using SQL Server 2000.

Thanks,

-Long

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-30 : 01:55:51

Query Refresh table in query analyzer to check file path stored, and ensure it exists on your server.

nguyenl
Posting Yak Master

128 Posts

Posted - 2007-11-30 : 12:43:11

Hi,

It is currently working now bu only on the in the dir is poiting to the "C". What if the backups are on another partion such as the "E"? I am getting the error:

Server: Msg 3038, Level 16, State 1, Line 13
The filename '' is invalid as a backup device name. Reissue the BACKUP statement with a valid filename.
Server: Msg 3013, Level 16, State 1, Line 13
RESTORE DATABASE is terminating abnormally.

Please help....

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-02 : 00:06:56

Does sql server have e:\ drive?

nguyenl
Posting Yak Master

128 Posts

Posted - 2007-12-03 : 14:26:54

No it is a second partition. The full Path is E:\Backup\MSSQL. I can export the information to the table I have created above.

After running this code:

create table Refresh (dir varchar(2000))
insert into Refresh exec master.dbo.xp_cmdshell 'dir e:\Backup\MSSQL\Northwind_db_*.bak /b'
declare @filename varchar(500)
select @filename = max(dir) from Refresh where dir like 'Northwind_db_%.bak'
select backup_file= @filename
Drop table Refresh
Restore database Northwindtest
From Disk = @filename

I get this error:

(9 row(s) affected)

(1 row(s) affected)

Server: Msg 3201, Level 16, State 2, Line 13
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Northwind_db_200711300950.BAK'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 13
RESTORE DATABASE is terminating abnormally.

avmreddy17
Posting Yak Master

180 Posts

Posted - 2007-12-03 : 15:32:27

Are you running the Script on the Server or not. If you run the script from your local machine then it looks for the C: Drive
on your local box. Run it on the Server itself and make sure you pass the @fileName correctly

Venu

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-03 : 15:37:50

quote:
Originally posted by avmreddy17

Are you running the Script on the Server or not. If you run the script from your local machine then it looks for the C: Drive
on your local box. Run it on the Server itself and make sure you pass the @fileName correctly

Venu

This is not true. xp_cmdshell, backup, restore, ... run from the server regardless of where you are running the code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-03 : 16:00:17

Well, the backups are stored at "e:\Backup\MSSQL\", but when restoring them, SQL Server uses the information about original place for the databasefile which is stored in the databasefile itself.

Also, what do this line do?
select backup_file= @filename

E 12°55'05.25"
N 56°04'39.16"

nguyenl
Posting Yak Master

128 Posts

Posted - 2007-12-03 : 17:32:53

Thankyou everyone. I finally got it to work. The code I used was:

create table Refresh (dir varchar(2000))

insert into Refresh exec master.dbo.xp_cmdshell 'dir e:\Backup\mssql\Northwind_db_*.bak /b'

declare @filename varchar(500)

select @filename = max(dir) from Refresh where dir like 'Northwind_db_%.bak'

drop table refresh

Set @filename = 'e:\Backup\mssql\' + @filename

restore database Northwindtest

from disk = @filename

nguyenl
Posting Yak Master

128 Posts

Posted - 2007-12-03 : 17:41:12

Thankyou, Michael Jones!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources