Copy entire database to another server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Copy entire database to another server

Author

Topic

algola
Starting Member

5 Posts

Posted - 2009-04-19 : 01:12:25

Hi,

I have two SQL Server running on the same LAN.
I dont know I can copy entire database that run on the first server to the other, automatically. I use VB6 to develop my application.

regards
ale

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-04-19 : 16:30:18

back it up and restore it to the other server, you can do this using the SQL BACKUP and RESTORE commands.

algola
Starting Member

5 Posts

Posted - 2009-04-20 : 11:40:42

wow!

have you a simple example of code?

thanks
ale

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-04-20 : 11:49:38

There is a simple example of the SQL code in Books Online.

Backup: http://msdn.microsoft.com/en-us/library/ms186865.aspx

Restore: http://msdn.microsoft.com/en-us/library/ms186858.aspx

These will need to be put into a connection object and run, I'm sure there are examples on the web of how to create a connection object.

algola
Starting Member

5 Posts

Posted - 2009-04-20 : 12:22:02

great!

algola
Starting Member

5 Posts

Posted - 2009-04-22 : 03:33:29

Cn.Open "DRIVER={SQL Server};SERVER=" & MyServer & "; DATABASE=" & MyDb & "; UID=...;PWD=...;"

Cn.Execute ("BACKUP DATABASE [PAPIRO] TO DISK = N'c:\Programmi\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup\PAPIRO.bak' WITH NOFORMAT, INIT, NAME = N'PAPIRO-Completo Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 1")

Cn.Close
Cn.Open "DRIVER={SQL Server};SERVER=" & MyServer_locale & "; DATABASE=...; UID=sa;PWD=...;"
Cn.Execute ("RESTORE DATABASE [papiro_locale] FROM DISK = N'C:\Programmi\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup\papiro.bak' WITH FILE = 1, MOVE N'PAPIRO' TO N'c:\Programmi\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\papiro_locale.mdf', MOVE N'PAPIRO_log' TO N'c:\Programmi\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\papiro_locale_1.ldf', NOUNLOAD, REPLACE, STATS = 10")

that's all

algola
Starting Member

5 Posts

Posted - 2009-04-22 : 03:38:57

now i have only a little problem with an error occurred when database [papiro_locale] is in use.
can i close autimatically any connection an work with restoring?

algola!

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-04-22 : 04:05:30

As you are copying the restore over an existing database, you will get these problems. The only thing I will say is that be very sure before you kill users as if they are external (or sometimes even internal) customers, they may not be happy about being kicked out in the middle of doing something. There is a script here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40077

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 04:12:29

Change your connection string to MASTER default database.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources