Improve company productivity with a Business Account.Sign Up

x
?
Solved

backup and restore from dos prompt

Posted on 2002-06-29
4
Medium Priority
?
253 Views
Last Modified: 2012-05-04
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
Comment
Question by:sanjaykattimani
  • 2
  • 2
4 Comments
 
LVL 9

Accepted Solution

by:
miron earned 600 total points
ID: 7119012
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
 
LVL 3

Author Comment

by:sanjaykattimani
ID: 7121465
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
 
LVL 3

Author Comment

by:sanjaykattimani
ID: 7121483
Let me know how do i restore the db.
0
 
LVL 9

Expert Comment

by:miron
ID: 7124000
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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

606 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question