October 29, 2009 at 3:28 am
Test db still in restoring state after restored live db over test db
I have restored a live database over a test database. However i did not backup the tail log of the test database before i restored the live database on top of it.
i used the following script
USE master
GO
/*
ABC = production database
ABCTEST = test database
*/
RESTORE DATABASE [ABCTEST]
FROM
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ABC_backup_200910212030.bak' -- edit file name to match
WITH
FILE = 1, -- normally this would be = 1
MOVE N'ABC' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ABCTEST.mdf',
MOVE N'ABC_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ABC_log.LDF',
NOUNLOAD,
REPLACE,
STATS = 10
GO
ALTER DATABASE [ABCTEST] MODIFY FILE (NAME=N'ABC', NEWNAME=N'ABCTEST')
GO
WHICH WILLIAM MITCHELL POSTED
How do i bring the database out of the restore state?
Thanks,
Iain.
October 29, 2009 at 3:52 am
Are you sure that the restore has completed successfully.
I dont see with recovery in your script.
What you could do is
restore database [databasename] with recovery
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 29, 2009 at 4:08 am
I tried the restore database statement and got the following message
Msg 3153, Level 16, State 2, Line 1
The database is already fully recovered.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
How could the db still be in the restoring state then.
October 29, 2009 at 4:11 am
run this
select [name],state_desc from sys.databases
maybe you need to refresh your view on the databases
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 29, 2009 at 4:14 am
i managed to get it working. The issue was with the management studio not reporting correctly.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply