Restore Database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Restore Database

Author

Topic

arusu
Yak Posting Veteran

60 Posts

Posted - 2011-08-18 : 08:56:19

Hi, I was just wondering what the command is for restoring a database but also renaming it. So to restore it, I know it's

RESTORE DATABASE @DatabaseName FROM DISK = @Path. So this will rename it on SQL Server but not in this path "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA"

If someone has an answer, please make it very clear as I am new to this.

curtis.sujata
Starting Member

3 Posts

Posted - 2011-08-18 : 09:27:49

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.ldf'

/*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

sujata curtis

Kristen
Test

22859 Posts

Posted - 2011-08-18 : 10:11:17

have a look at the Restore command synopsis here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54300

arusu
Yak Posting Veteran

60 Posts

Posted - 2011-08-18 : 10:35:40

Thanks curtis but I also need the path for the defaultData and DefaultLog which I'm not sure how to get other then looking at the registry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources