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

basit 1 gravatar image

basit 1
499 51 63 85

(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

Tim gravatar image

36.4k 38 41 139

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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: 1056 times

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