Restore large number of database on a server.
-
How to restore large number of databases on a server at a time? This is required in a situation where I need to restore around 300 databases from their backup files to a new server and I don't want to create and restore individually each and every DB instead of that I want to create and restore all the databases at a time,so that I can reduce the recovery time.
-
You technically still end up restoring them individually or at least in batches. You could create a script of restoring and break the script in multiple pieces. Then you can open up a session for each portion of script to run them. However I wouldn't try to many at one time as the resources to unpack and place in the right location will take quite a few cycles the more you add to be done. Maybe try 6 at a time (break the big script into 6 littler ones).
-
Unless you're running a Unisys ES7000, or all your databases are really small, I wouldn't restore more than one at a time. What happens if one restore doesn't work properly? Do you continue loading the rest regardless? Also, database restores cause a lot of Disk I/O and you could run into problems with Disk Read/Write queues.
What I would do is maintain a table that lists each database, its backup file and a flag to indicate if it's to be restored or not. Then write a stored procedure that extracts all the entries that need to be restored. Then it would build a dynamic RESTORE DATABASE statement, execute it, check that it completed successfully and move on to the next database in the list.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
If the servers are set up identically as in the case of a backup/test server you can stop the services on the machine to be restored and copy the database files - including master - (from the active machine) there. This only works if the machines are set up identically with the same paths etc. You will also need to adjust the name of the server in sysservers.
If you need to recover from backups only write some scripts: use some of the tricks the other authors have suggested. You can generate the commands by using a select from the sysdatabases table as in:
select 'restore database ' +name+ 'etc....' from master..sysdatabases
where name not in ('tempdb', 'master', 'msdb', ...)
-> execute the command in Query Analyser and paste the results into a QA window on your backup server ...
-
It generates the restore commands, that you can run from the SQL Query Analyzer. It relies on the 'Restore filelistonly '
to get the backups and constituent dat files. You can make it parameter driven if you do often with different parameters.
In my case I always do it from same location. Let us know if it works for you.
'-- DBA Script
'-- Author: ---------
'-- 7/31/03
'-- Generate Restore SQL Statements to Restore databases from Files in a directory passed as a parameter
'-- The backup files path is hardcoded. Update I:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\ with the location of your backup files
'-- The data file path where the backups are restored is hardcoded. I:\Program Files\Microsoft SQL Server\MSSQL\DATA\. This can be changed.
'-- Check whether the directory where the database backups are stored is passed as a parameter
Set objArgs = Wscript.Arguments
If objArgs.Count<> 1 then
Wscript.Echo "Invalid argument numbers. Provide directory of the backup files."
Wscript.Echo "Usage : RestoreScript.vbs G:\Backups"
Wscript.Quit
End If
Dim fso, f, f1, fc, s, objDataConn1, sql
Set objDataConn1 = wscript.CreateObject("ADODB.connection")
objDataConn1.open "DSN=Devdb911","sql_audit","audit4$QL"
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder(objArgs(0))
Set fc = f.Files
For Each f1 in fc
' Get the database name from the filename
if instr(f1.name, "_db_") > 1 then
dbname = Left(f1.name, instr(f1.name, "_db_") - 1 )
wscript.echo "Select '"& "Restoring database " & dbname & "'"
wscript.echo "go"
backuppath= "'I:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\" & f1.name &"'"
sql = "restore filelistonly from disk = 'I:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\" & f1.name &"'"
wscript.echo "RESTORE DATABASE [" & dbname & "] FROM DISK = N" & backuppath
wscript.echo "WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERY , "
Set rs = wscript.CreateObject("ADODB.RecordSet")
RS.Open sql, objDataConn1
Datafile = mid ( RS(1).value, instrrev(RS(1).value, "\") +1, len(RS(1).value))
MoveStr = "Move N'" & RS(0).value & "' to N'I:\Program Files\Microsoft SQL Server\MSSQL\DATA\" & Datafile & "'"
RS.Movenext
Do While Not RS.EOF
wscript.echo movestr & ","
Datafile = mid ( RS(1).value, instrrev(RS(1).value, "\") +1, len(RS(1).value))
MoveStr = "Move N'" & RS(0).value & "' to N'I:\Program Files\Microsoft SQL Server\MSSQL\DATA\" & Datafile & "'"
RS.Movenext
Loop
wscript.echo movestr
wscript.echo "go"
end if
Next
-
How about try
select ' RESTORE DATABASE ' + name + ' from ' + name + '_dat'
from sysdatabases
where name not in ( 'model', 'northwind', 'pubs','tempdb')
This will give you a list of all the database that needed to be restored. Copy this result from the active/1st server and put it on the new server/2nd to execute.
Assumption:
1.The device exist on the 2nd server.
2.The SQL-Server Agent is stoped. (for loading msdb)
3.I usually load master 1st and get the login working first before doing the rest of the databases.
You can restored all of the databases at the same time just make sure to read the message and look for error. Take note if there is error and correct them later.
After the load is done you will have to do exec sp_dbchangeowner 'sa'
exec sp_dbchangeowner 'olddboname'
so that it will have the same dbo as your original server.
mom
-
You could run a query get the list of databases from the server where u got the backups from ....and give the query the location of the backups on ur new server...
And just run them ....I think it shud do the trick...
Cheers!
Arvind
Arvind -
Thanks for your reply.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply