Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

backup and restore from dos prompt

I am looking for dos command to take backup and restore complete database of sql server 7.0 so that i can write script to automate backups and restore whenever required.
0
sanjaykattimani
Asked:
sanjaykattimani
  • 2
  • 2
1 Solution
 
mironCommented:
did you try

osql -S<server_name> [ -U<user_name> -P<password> | -E ] -Q"BACKUp DATABASE <database_name> to disk = 'C:\back' WITH INIT"

or

osql -S<server_name> [ -U<user_name> -P<password> | -E ] -i <command_file_path>

in the file located at <command_file_path> save command
BACKUp DATABASE <database_name> to disk = 'C:\back' WITH INIT


this file may contain entire sql script where you can concatenate backup file name using getdate() or any other sql server function, so that a unique file name created each time. Also, it can delete existing backups older then certain time using xp_cmdshell extended stored procedure. It can also follow a set of rules what type of backup to create - full backup, differential backup, log backup, or even more advanced backup operations.

sql server enterprise manager offers a pretty cool gui based wizard that will walk you through a set of questions to create a scheduled maintenance plan that will set off backup process on its own, I would yield that it offers less control over the backup process then a transact sql script, but it is worthy feature to learn and most of the time it is more then enough to have a good backup process in place.
0
 
sanjaykattimaniAuthor Commented:
Great! osql works and i am able to take the backup. Now how to restore the same?
when i used the following command

C:\WINDOWS\Desktop>osql -Ssql_domain -Usa -P -Q"restore database master from dis
k='c:\masterdb'

It generated following command
Msg 3108, Level 16, State 1, Server SQL_DOMAIN, Procedure , Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE DATABASE must be used in
single user mode when trying to restore the master database.
Msg 3013, Level 16, State 1, Server SQL_DOMAIN, Procedure , Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation
terminating abnormally.
 
0
 
sanjaykattimaniAuthor Commented:
Let me know how do i restore the db.
0
 
mironCommented:
osql -S<server_name> [ -U<user_name> -P<password> | -E ] -i
<command_file_path>
in the command file copy/paste following script

---
use master

ALTER DATABASE <database_name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

restore database <database_name> from disk = '<file_path>' [ with replace]"

ALTER DATABASE <database_name> SET MULTI_USER

-- with replace will force restore over existing database.
---

Cheers
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now