Restoring .bak files using query Analyzer [sql server]
February 16, 2008
I was wondering how to restore BAK files using query Analyzer as most of the hosting services will provide query Analyzer.
I got a script which works wonder and it’s damn simple.
1. create .bak file using Enterprise Manager
2. Store that in some location
3. Open Query analyzer and execute below command.
Use Master
Alter Database db_Name
SET SINGLE_USER With ROLLBACK IMMEDIATE
RESTORE DATABASE db_Name
FROM DISK = ‘D:\dbName.bak’
That’s it here we go………..
One more solution is installing third party software http://www.sqlscripter.com/
Entry Filed under: SQL. .
3 Comments Add your own
Leave a Comment
Some HTML allowed:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <pre> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>
Trackback this post | Subscribe to the comments via RSS Feed
1.
Raj kumar | November 14, 2008 at 7:21 am
Thanks for the query.
works perfact
2.
Mario | February 3, 2009 at 3:24 pm
Database YourDB has full backup YourBaackUpFile.bak. It can be restored using following two steps.
Step 1: Retrive the Logical file name of the database from backup.
RESTORE FILELISTONLY
FROM DISK = ‘D:BackUpYourBaackUpFile.bak’
GO
Step 2: Use the values in the LogicalName Column in following Step.
—-Make Database to single user Mode
ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
—-Restore Database
RESTORE DATABASE YourDB
FROM DISK = ‘D:BackUpYourBaackUpFile.bak’
WITH MOVE ‘YourMDFLogicalName’ TO ‘D:DataYourMDFFile.mdf’,
MOVE ‘YourLDFLogicalName’ TO ‘D:DataYourLDFFile.mdf’
/*If there is no error in statement before database will be in multiuser
mode.
If error occurs please execute following command it will convert
database in multi user.*/
ALTER DATABASE YourDB SET MULTI_USER
GO
3.
steve | May 28, 2009 at 11:15 pm
Can one restore just certain tables and data?