February 24, 2010 at 10:14 am
Hi all,
I create a maintenance plan that backups all my databases to a backup folder on another server. The backups are created with current date, for example: database1_20100224.bak, database2_201002224.bak and Database3_20100224.bak
On the destination server I would like to run an automated restore job to restore all the database for that given date. How can I accomplish this task. Using any SQL version
Any suggestions will be greatly appreciated.
February 24, 2010 at 10:48 am
I would recommend using Powershell and SQLCMD - with powershell you can parse a directory for the latest file(s) by date, and build a string to execute the restore that would be passed to SQLCMD.
You can find all kinds of examples on this site and the web.
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
February 24, 2010 at 10:57 am
Thanks and I will look in to this.
What I have so far is this. I have taking the statement that backups the database and modified it. The issues I have are as follows
1. The database must exists and will be overwritten
2. Since the databases are from a different server the file location are different.
3. How can I use the with move with this statement
Here is the state so far:
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName NVARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path='C:\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETUTCDATE(),112)
SELECT name,flag=0 into #tempbackup FROM master.dbo.sysdatabases
WHERE name IN ('DBA')
set rowcount 1
WHILE (exists(SELECT * FROM #tempbackup WHERE flag=0))
BEGIN
Select @name=name from #tempbackup WHERE flag=0
SET @fileName=@path + @name + '_' + @fileDate + '.BAK'
RESTORE DATABASE @name FROM DISK = @fileName
Update #tempbackup set flag=1 WHERE flag=0
END
set rowcount 0
drop table #tempbackup
February 24, 2010 at 11:08 am
In order for you to be able to use the WITH MOVE option, you are going to have to know the logical names of all of the files for that database (mdf/ndf/ldf). Then, you have to know where you are going to move those files - and finally, you have to identify whether or not that database already exists or just use the REPLACE option.
You can get all of that information using SMO (through powershell) or using SQLCMD to execute RESTORE FILELISTONLY (look this up, not sure of exact syntax at the moment).
Again, these kinds of scripts have already been written. Search this site and the web and you'll find plenty of options. Find the one that is close to what you want to do and customize it to your environment.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply