Restoration of multiple Database


I have a SQL server which contain 100 Database , Now i have to take the backup of All Database from This Server and Restore it at another server , Is There any Script for taking Backup and then i can restore it...

Thanks Basit

more ▼

asked Sep 16, 2010 at 02:21 PM in Default

avatar image

basit 1
509 56 65 91

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Will your drive letters be the same between the two servers? If so you a multitude of methods (loop, cursor, etc) to backup each database to disk, then can do the same to restore to disk. It will require several variables for the path, dbname, etc. Let me know if you need help writing the script.

more ▼

answered Sep 16, 2010 at 02:26 PM

avatar image

40.4k 39 84 166

Here is a small example of such script. Execute the script below on your source server (with results to text option), it will just generate both backup and restore scripts. Modify the paths as needed. Then you can copy the backup part of the script and execute it on the source, mobe the back files to destination and execute the restore part at destination:

declare @back_path varchar(255); declare @rest_path varchar(255);

select @back_path = 'C:\Dump\', @rest_path = 'D:\Dump\';

  • backup script, execute on the source server select 'backup database ' + [name] + ' to disk=N''' + @back_path + [name] + '.bak''; go

' from sys.databases where database_id > 4;

  • restore script, execute on destination server select 'restore database ' + [name] + ' from disk=N''' + @rest_path + [name] + '.bak''; go

' from sys.databases where database_id > 4;

set nocount off; go

  • this spells out the script like this: backup database SomeDB1 to disk=N'C:\Dump\SomeDB1.bak'; go

  • etc restore database SomeDB1 from disk=N'D:\Dump\SomeDB1.bak'; go

Sep 16, 2010 at 04:26 PM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 16, 2010 at 02:21 PM

Seen: 1183 times

Last Updated: Sep 16, 2010 at 02:21 PM

Copyright 2016 Redgate Software. Privacy Policy