March 27, 2008 at 1:08 pm
My boss wants a disaster recovery plan for our server. He wants to put it on external hard drive to be brought home. Losing a couple of days of data isn't critical but he wants to be able to bring the company back up and running with the least amount of effort and time. We need to know a step by step procedure how to run a script from the command line as opposed to from within the SQL manager (ie, how do I run a SQL script from the command line?). Is this possible? Without buying expensive software?
March 27, 2008 at 4:44 pm
not quite sure why or what you want to copy to a hard disk and why you need to do from the command line, but SQL does come with a utility to run SQL statements from the command line, See 'sqlcmd' in books Online (BOL). there is also osql for backwards compatibility to sql 2000.
---------------------------------------------------------------------
March 28, 2008 at 8:56 am
He wants to know the steps to put the data back onto the system(new server) if the old server gets destroyed in a fire or something. He doesn't want to do it over the internet. He wants to know the script to write on the command line to the new server to get the system up and running like it was. He wants to do a complete backup every two weeks and bring the hard drive(or whatever he wants to use) home. Day to day info isn't so critical at my company.
March 28, 2008 at 9:01 am
Yes I'm new at this and a little over my head. But if I can just get the info he wants then he will be able to do the rest. I know it's not your run of the mill disaster recovery plan. If anyone can help me that would be great. Try and keep it simple as possible for me to understand please.
March 28, 2008 at 9:07 am
Here's some of it in his words. Hopefully it will make what he wants more clear to everyone.
He wants to run a backup & restore scripts without being logged into the SQL Management Console.
Again, any help would be greatly appreciated.
March 28, 2008 at 9:09 am
If there's more steps after that I need to know them also.
March 28, 2008 at 9:48 am
hmm, I dont think this works quite as you are expecting. I'm presuming a small company here with one server, small amount of data?
In management studio create a maintenance plan to backup your databases to a local drive.
Or just a sqlagent job to back up the databases.Sql for a backup is
backup database dbname to disk = 'full pathname\dbname.bak' with init.
Or put the sql commands in a text file and use that as input via sqlcmd utility, scheduled via windows scheduler.
Maintenance plan has advantage of being able to keep more than one copy and handle the cycling out of old backups for you.
Use copy or xcopy to copy your backups to the media you wish to take off site.
If you lose your server you are going to have to rebuild it, reinstall OS and then reinstall sQL 2005 from scratch. You can then restore your databases, using sqlcmd for the master database and any method for the others. SQL to restore a database is:
restore database dbname from disk = 'full pathname to .bak file' with replace.
Look in BOL for info on 'reinstalling, 'recovering master database', 'backup database' and 'restore database'
There are many possible DR scenarios, so I recommend a web search on installing SQL and disaster recovery, and find a server to practice on! (even sql express on your laptop)
---------------------------------------------------------------------
March 28, 2008 at 10:05 am
Yes it is a small company for now. Otherwise it wouldn't be practical to try this.
March 28, 2008 at 1:27 pm
'Disaster Recovery' is a bit vague. Depends on the level of the disaster.
However you approach this, what you will want to do is copy the full backup of the database to a CD / DVD (if you're a small company, it should fit).
Then go to a brand new / completely clean machine, load the operating system, then load SQL Server 2005, then go into SQL Server 2005 and run a restore. It's absurd to require the 'restore' without using SQL Server environment. It almost sounds like he's looking to steal the information, not restore it. If he wants to learn how to execute a restore using T-SQL, then there are manuals to describe that (BOL, etc).
There is absolutely no point in trying to restore the database without using SQL Server. If he wants to restore to a different database platform (e.g., DB2), it won't work.
IF he doesn't understand databases, then he needs to redefine the task. If he understands databases, then the question sounds like 'what's the restore command format, customized for our operating environment?'
Vague questions will beget vague answers. And I can be as vague as anyone.
And, using sqlcmd IS using SQL Server 2005, just not using the GUI. So the DR needs to specify that the user may not be working from anything more sophisticated than a teletype device using a commande.exe window. [at least, that's how your question sounds to me].
The 'simplest' answer is to give him a formatted RESTORE command using the appropriate settings for your environment. That's one line of code.
This isn't in response to an audit, is it?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply