Restore database

Question

Restore database

rds 20124

Grasshopper

Points: 21
More actions
December 13, 2010 at 3:05 am

#232020

I have restored a database from another server. The script runs fine but the database shows up in MSSM as Database (Restoring...). Is there anything I should do to finish the job?
Anders

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Post the exact script you used.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rds 20124 Grasshopper Points: 21 More actions · Answer 2

I have used this:

Use Master

Go

RESTORE DATABASE [rotary1] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\rotary.bak'

WITH NORECOVERY,

MOVE N'Rotary_Primary' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\rotary1_1.mdf',

MOVE N'Rotary_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\rotary1_2.mdf',

MOVE N'Rotary_Index' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\rotary1_3.mdf',

MOVE N'Rotary_Text' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\rotary1_4.mdf',

MOVE N'Rotary_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\rotary1_1.ldf',

REPLACE

GO

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

rds 20124 (12/13/2010)
I have used this:
Use Master
Go
RESTORE DATABASE [rotary1] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\rotary.bak'
WITH NORECOVERY,
MOVE N'Rotary_Primary' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\rotary1_1.mdf',
MOVE N'Rotary_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\rotary1_2.mdf',
MOVE N'Rotary_Index' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\rotary1_3.mdf',
MOVE N'Rotary_Text' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\rotary1_4.mdf',
MOVE N'Rotary_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\rotary1_1.ldf',
REPLACE
GO

WITH NORECOVERY means restore the database but do not bring it online so that further backups (diff or log) can be restored. By using that option, you specifically requested that the database remain in the Restoring state after the restore finished.

If you want the database to be online at the end of the restore, you need to specify WITH RECOVERY, or leave the option out entirely as WITH RECOVERY is the default.

To bring the database online, run this.

RESTORE DATABASE [rotary1] WITH RECOVERY

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

NickDBA Hall of Fame Points: 3792 More actions · Answer 4

Shouldn't he be using ".ndf" for his secondary files? I thought you could have only one ".mdf"?

Steve Jones - SSC Editor SSC Guru Points: 736760 More actions · Answer 5

Only for convention. These are just file names and extensions, no relation to how SQL Server uses the files.

rds 20124 Grasshopper Points: 21 More actions · Answer 6

You have made my day. I have read endless articles and consulted numerous eggheads without any results. It works now

Thanks

🙂