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.
| Author |
Topic |
|
NgKH
Starting Member
15 Posts |
 Posted - 2003-04-02 : 09:44:34
|
| How do you write a back up and restore scripts on Query Analyer to back up a database to a backup file and restore it to another server with that file? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-04-02 : 10:49:23
|
| Look at backup database and restore database in bol.It is better to transfer the backup to the other server before the restore. For this use master..xp_cmdshell with a dos copy command.I perform the backup on the destination server by running a scheduled task which waits for the file to turn up (it is copied then renamed).I posted some code on this site a while ago which would back up all databases - you can look at that for the backup command.You may need to move the files on the restore but apart from that it's similar.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-02 : 15:34:25
|
| Here is an example:BACKUP DATABASE DB1 TO DISK = 'E:\MSSQL\Backup\DB1.bak'xp_cmdshell 'copy E:\MSSQL\Backup\DB1.bak \\Server2\e$\mssql\backup'RESTORE DATABASE DB1 FROM DISK = 'E:\MSSQL\Backup\DB1.bak' WITH REPLACEOf course you might need to change the RESTORE command so that it moves the files around, but that all depends on your environment.Tara |
|
|
|
|
|
|
Back Up and Restore Scripts