February 23, 2009 at 12:20 am
Hi guys, I am quite new to SQL but need to be able to restore a database using the command line. Does anyone know how to accomplish this?
Or is there another way in which you could schedule a restore?
February 23, 2009 at 7:38 am
Can you explain what you mean by command line would TSQL or OSQL be ok?
February 23, 2009 at 7:50 am
jakemike1 (2/23/2009)
Hi guys, I am quite new to SQL but need to be able to restore a database using the command line. Does anyone know how to accomplish this?
Look up SQLCMD in books online.
Or is there another way in which you could schedule a restore?
SQL Agent?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 23, 2009 at 8:03 am
If you are not that familiar with SQL, you can use the GUI to schedule a restore. Go into Management Studio, use the GUI screens as if you're doing a restore. Select the database, backup file, restore type etc... When everything is selected correctly, instead of pressing the final "OK", go up to the "Script" button and choose "Script Action To Job". This will generate the T-SQL commands and put them into a job.
This will help you see the coding used for common functions.
You then just need to set the schedule time. If you have email enabled, you can have the job send an email when it completes so you know if it suceeded or failed.
February 23, 2009 at 8:17 am
Yes you can do it via the commandline by using osql or sqlcmd depending which version of sql you are using.
SQL 2000 =osql
SQL 2005 =sqlcmd
use master
go
restore database dbname from disk ='filepath' with stats=10
go
or you can copy the code and put it in a job
Hope this helps
February 23, 2009 at 2:25 pm
do you need to restore the db over the original or restore as another name?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 23, 2009 at 7:44 pm
Thanks for all the info guys, and as I'm rather new to SQL I have taken your suggestion Ten Centuries and created a script using the GUI.
Appreciate all the help.
February 24, 2009 at 1:34 am
jakemike1 (2/23/2009)
Thanks for all the info guys, and as I'm rather new to SQL I have taken your suggestion Ten Centuries and created a script using the GUI.
There's no one here named "Ten Centuries"
Those are the forum ratings, which depend on number of posts, etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 24, 2009 at 2:20 am
Rather than using Enterprise Manager / Management Studio to script backups / restores i would get used to writing TSQL to do the job not only is it faster but when it comes to point in time restores you won't encounter any errors.
February 24, 2009 at 2:58 am
you can open enterprise manager and use query an or open sql server manager and new query or you can use osql or sqlcmd depending if you are using 2000 or sql 2005 and above.
osql -S server -E
sqlcmd -S servername -E
RESTORE DATABASE dbname FROM TAPE = N'\\path\dbname\dbname.BAK' WITH FILE = 1, NOUNLOAD, STATS = 10
GO
February 24, 2009 at 12:28 pm
GilaMonster (2/24/2009)
jakemike1 (2/23/2009)
Thanks for all the info guys, and as I'm rather new to SQL I have taken your suggestion Ten Centuries and created a script using the GUI.There's no one here named "Ten Centuries"
Those are the forum ratings, which depend on number of posts, etc.
"Ten Centuries" refers to the length of time I've been searching for answers to SQL questions !
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply