January 9, 2009 at 7:39 am
We are using MS Sql 2005 SP2. Have a need to backup 9 databases (full recovery) and be able to restore the same 9 databases on a frequent basis. Was able to create a maintenance plan to backup the databases to a specific backup directory. Now want the ability to do the same but as a restore. Did not see a restore option in maintenance plan.
How do you tell SQL to restore databases 1-9 from backup directory A to data directory B and do a full restore, overwriting any existing files?
I know how to do this manually (right click on DB, Tasks, Restore.....etc.) but looking for a way to restore all 9 databases by some automatic process.
Thanks.
January 9, 2009 at 11:23 am
You need to go back to the TSQL that the GUI is doing for you. You can script all the restores using:
RESTORE DATBASE X FROM DISK/DEVICE = 'Something' WITH REPLACE, MOVE 'LogicalDevice' TO 'PhysicalLocation'
,ETC
For more details hit the Books Online, any of the great backup articles here at SSC or this article over at Simple Talk[/url].
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 12, 2009 at 7:19 am
I am confused about 2 things regarding the restore script:
1) What is the "logical device" and how is that usually expressed? Same question for the physical device location?
How does this work? How does the database get restored to the proper database and location?
2) I would like the restore script to restore multiple databases as a set. Will the restore script restore the databases sequentially?
How does the script now when database 1 has been successfully restored before starting to restore database 2, etc. ?
January 12, 2009 at 9:28 am
rons (1/12/2009)
I am confused about 2 things regarding the restore script:1) What is the "logical device" and how is that usually expressed? Same question for the physical device location?
How does this work? How does the database get restored to the proper database and location?
2) I would like the restore script to restore multiple databases as a set. Will the restore script restore the databases sequentially?
How does the script now when database 1 has been successfully restored before starting to restore database 2, etc. ?
1) You should read the article and read Books Online. The physical device is literally the file location. The logical device is the logical file name that is part of the datase definition. You'll need to specify these for each database and the physical storage could be different from one machine to the next.
2)You can create on script that does lots of databases, which will be serial, or you can create multiple scripts run from multiple locations, which will be parallel.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 13, 2009 at 1:25 pm
The hardest part about automating your restore process is grabbing the latest backup file name from the location you are backing up to.
-- Get Backup location and filename ------------------------------------------------------
DECLARE @FileList TABLE(BackupFileName VARCHAR(255))
DECLARE @FileName AS VARCHAR(100)
DECLARE @FolderPathWithSlash AS VARCHAR(250)
DECLARE @DirCommand AS VARCHAR(500)
SET @FolderPathWithSlash = '\\location\of\backupfile\'
SET @DirCommand = 'DIR ' + @FolderPathWithSlash + 'dbname_*.bak /b /O-D'
INSERT @FileList
EXEC master.dbo.xp_cmdshell @DirCommand
SELECT TOP 1
@FileName = BackupFileName
FROM
@FileList
DECLARE @TargetDatabaseName VARCHAR(255)
SET @TargetDatabaseName = 'dbname'
DECLARE @BackupPathName VARCHAR(500)
SET @BackupPathName = @FolderPathWithSlash + @FileName
DECLARE @TargetDirPath VARCHAR(500)
SET @TargetDirPath = 'H:\DATA\dbname\'
DECLARE @TargetLogPath VARCHAR(500)
SET @TargetLogPath = 'G:\LOGS\dbname\'
DECLARE @dbname VARCHAR(500)
SET @dbname = @TargetDirPath + @TargetDatabaseName + '.mdf'
DECLARE @dbnameLog VARCHAR(500)
SET @dbnameLog = @TargetLogPath + @TargetDatabaseName + 'Log.ldf'
-- Restore DB -----------------------------
RESTORE DATABASE @TargetDatabaseName FROM DISK = @BackupPathName
WITH REPLACE,
MOVE 'dbname' TO @dbname,
MOVE 'dbnameLog' TO @dbnameLog
January 13, 2009 at 10:56 pm
i have the following idea :
save all your information about your backup(name od database and path of file ....) to table , and then make SP which will contain courser to read each raw in created table and then make backup, then make job to run this sp any time you want. if you have problem in writing code, tell me and i will send you this/
about my self i use veritaz (symantic), it handels this situation in very good way without any problems 😉
January 14, 2009 at 8:30 am
GOOGLE "SQL Server Auto Restore script", you'll probably hit a lot
Or just search on SSC
Above idea is exactly what you should do
Look into msdb tables - backupset, backupmediafamily
for physical names (file path & filename)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply