Scripting Backup/Restore - SQL Server Forums

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

Scripting Backup/Restore

Author

Topic

Burntchips
Starting Member

4 Posts

Posted - 2010-05-20 : 10:40:03

Hello,

In sql server 2005 I have a need to create a script that will backup a database from one server and restore it on another. Ideally I would like something that creates the CREATE and INSERT statements for an entire database and saves them to a file so I can execute them on another server but I can't seem any tool that comes with SQL Server that does this. I know I can do it through the management console but I'm trying to create a batch script.

Is this possible?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-20 : 13:29:25

Why don't you do it the simple and just backup the source database, copy the backup file to the destination server, and run a restore?

BACKUP DATABASE
xcopy
RESTORE DATABASE

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Burntchips
Starting Member

4 Posts

Posted - 2010-05-20 : 13:31:39

quote:
Originally posted by tkizer

Why don't you do it the simple and just backup the source database, copy the backup file to the destination server, and run a restore?

BACKUP DATABASE
xcopy
RESTORE DATABASE

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Can I do this in a batch file? What command would I run? Something like 'sql.exe "BACKUP DATABASE"' ??

Mike

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-20 : 13:38:11

Yes you can do it in a batch file via sqlcmd.exe. Put all of the code into a sql file and then call the sql file via sqlcmd.exe. You can then call sqlcmd.exe from a batch file (cmd, bat).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-20 : 13:39:20

Here's examples of the SQL commands:

BACKUP DATABASE db1
TO DISK = 'F:\Backup\db1\db1_FULL.bak'
WITH INIT

...

RESTORE DATABASE dbA
FROM DISK = 'E:\Dir1\db1_Full.bak'
WITH REPLACE, RECOVERY --you may need MOVE option if the paths are different between servers

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources