February 14, 2014 at 8:21 am
Hi all,
Just wonder if I can get some help with a script that would allow me to restore 20 + .bak files to a new server with the data and logs in default location?
I imagine it would use Restore FileListOnly to populate a variable, assign the DB name to the logical name in the bak file with the '_***' removed, ie
Logical name StreetView_Data
Db name is StreetView
and then run something like (sorry this is the nearest I can visualise....)
Declare @DatabaseName Varchar
Declare @BackupLocationFile Varchar
Set @Databasename =
(Select Logical name (right-trim _*** )
From ((Restore FileListOnly From Disk = 'Backup file location'))
Restore Database @DatabaseName
From Disk = '@BackupLocationFile'
With Stats = 1
--And then loop through all 20 bak files. I am not sure how to assign the drive/folder where I've kept the bak files, but as long as it loops through ALL of them, that's cool.
February 14, 2014 at 9:00 am
Could you use Powershell? It'll make this task a lot easier.
In fact, here's a script that someone made earlier --> http://www.sqlservercentral.com/scripts/SQL+Powershell+Script/101648/%5B/url%5D.
February 14, 2014 at 11:38 am
Jake Shelton (2/14/2014)
Hi all,Just wonder if I can get some help with a script that would allow me to restore 20 + .bak files to a new server with the data and logs in default location?
I imagine it would use Restore FileListOnly to populate a variable, assign the DB name to the logical name in the bak file with the '_***' removed, ie
Logical name StreetView_Data
Db name is StreetView
and then run something like (sorry this is the nearest I can visualise....)
Declare @DatabaseName Varchar
Declare @BackupLocationFile Varchar
Set @Databasename =
(Select Logical name (right-trim _*** )
From ((Restore FileListOnly From Disk = 'Backup file location'))
Restore Database @DatabaseName
From Disk = '@BackupLocationFile'
With Stats = 1
--And then loop through all 20 bak files. I am not sure how to assign the drive/folder where I've kept the bak files, but as long as it loops through ALL of them, that's cool.
Cadavre is correct. You need Powershell, and also, you are missing restore options. Remember that every database is at least 2 and perhaps more files. There are options for replacing existing databases and the state into which you would wish to leave the database. It also sounds as if you wish to rename the files as you restore the database, e.g., "DBONE.MDF" to "DBONE_PRIMARY.MDF", which requires the WITH MOVE option and more than one result per database. You also have to parse the database name out of the file name.
This is technically possible if you create temporary tables to capture xp_cmdshell's results of a DIR of the folder holding the backups, and then process RBAR through the results to, parse, generate and execute RESTORE statements. If you can generate a script that you can execute, you might be able to avoid the RBAR.
If you will be constantly repeating this task, then it is worth your time. If this is a one-off job, it's much faster to use SSMS to individually restore each database.
Thanks
John.
February 14, 2014 at 11:44 am
I have an article with a tsql script on how to do that. You could use that script if you prefer. You will probably need to make some mods for your environment (like maybe throw it in a cursor and have it populate the cursor from some table with database names that should be restored).
I use this mechanism to do regular automated restores from one server to another server.
http://jasonbrimhall.info/2014/01/14/t-sql-tuesday-050-automating-database-restores/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply