restoring Full back and trans logs on diff sql srv

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

SQL Server 2000 Forums

SQL Server Administration (2000)

restoring Full back and trans logs on diff sql srv

Author

Topic

jimmpy
Starting Member

15 Posts

Posted - 2005-11-01 : 16:10:30

Here’s the scenario:
Some user accidentally updated the apprx 10000 records on Friday on prod.

I created the new db on sql test server and trying to restore Thursday night’s full backup with 3 tranlogs…because records were updated around in the afternoon.

When I use following script on another sql test server:

USE MyDB_PROD
RESTORE DATABASE MYDB_PROD
FROM DISK = 'D:\BACKUP\MYDB_PROD_db_2005272300.BAK'
WITH NORECOVERY

GO
RESTORE LOG MYDB_PROD
FROM DISK = 'D:\BACKUP\MYDB_PROD_tlog_2005280002.TRN'
WITH NORECOVERY
GO

RESTORE LOG MYDB_PROD
FROM DISK = 'D:\BACKUP\MYDB_PROD_tlog_2005280400.TRN'
WITH NORECOVERY
GO

RESTORE LOG MYDB_PROD
FROM DISK = 'D:\BACKUP\MYDB_PROD_tlog_2005280800.TRN'
WITH NORECOVERY
GO

RESTORE LOG MYDB_PROD
FROM DISK = 'D:\BACKUP\MYDB_PROD_tlog_2005281200.TRN'
WITH NORECOVERY
GO

RESTORE LOG MYDB_PROD
FROM DISK = 'D:\BACKUP\MYDB_PROD_tlog_2005281600.TRN'
WITH RECOVERY, STOPAT = 'OCT 28, 2005 1:25 PM'

I get error something like…**database is currently being used** even after killing all the login ids…still shows this message.

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2005-11-01 : 16:15:25

Take out the first line (USE MyDB_PROD) and run this from master. The login using the database is you. I expect, if the MyDB_PROD database is there, you will need to add REPLACE to the first restore command.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources