Restore DB without restoring all TLOG files

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

Import/Export (DTS) and Replication (2000)

Restore DB without restoring all TLOG files

Author

Topic

sunfun
Starting Member

5 Posts

Posted - 2009-09-29 : 11:50:00

I have a production database (PROD) that I need to restore weekly to another instance (MIGR) for reporting purposes. The PROD db has 3 transaction log files on seperate logical drives as it has many users. The MIGR db doesn't need all the 2 additional log files as it has a small uesr base with very little activity. I have written the following script that is working.

-- First Truncate the log files to make the backup and resulting restore smaller

USE CUDProd
GO
DBCC SHRINKFILE ('CUDProd_log', 1)
DBCC SHRINKFILE ('CUDProd_2_log', 1)
DBCC SHRINKFILE ('CUDProd_1_log', 1)
-- I know this type of backup breaks the chain, but I'm not worried about it as we do a full backup about 15 mins after this runs.
BACKUP LOG CUDProd WITH TRUNCATE_ONLY
DBCC SHRINKFILE ('CUDProd', 1)
DBCC SHRINKFILE ('CUDProd_2_log', 1)
DBCC SHRINKFILE ('CUDProd_1_log', 1)
GO

BACKUP DATABASE [CUDProd] TO DISK = N'M:\MSData\MSSQL$SQL2K_DB\BACKUP\CUDProd\CUDProd_Sunday_MID_BKUP.BAK' WITH INIT , NOUNLOAD , NAME = N'CUDProd_Sunday_MID_BKUP', NOSKIP , STATS = 10, NOFORMAT
GO

-Now do the restore
ALTER DATABASE CUDProd_Migr
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
GO

RESTORE DATABASE CUDPROD_Migr
FROM DISK = 'M:\MSData\MSSQL$SQL2K_DB\BACKUP\CUDProd\CUDProd_Sunday_MID_BKUP.BAK'
WITH MOVE 'CUDProd_Data' To 'S:\MSData\MSSQL$SQL2K_DB\Data\CUDProd_migr.mdf',
MOVE 'CUDProd_Log' TO 'L:\MSData\MSSQL$SQL2K_DB\Data\CUDProd_migr_log_2.ldf',
MOVE 'CUDProd_1_Log' To 'K:\MSData\MSSQL$SQL2K_DB\Data\CUDProd_migr_log_1.ldf',
MOVE 'CUDProd_2_Log' To 'J:\MSData\MSSQL$SQL2K_DB\Data\CUDProd_migr_log.ldf'
GO

ALTER DATABASE CUDPROD_Migr SET MULTI_USER
GO

The problem is that I don't want to restore these files:
MOVE 'CUDProd_1_Log' To 'K:\MSData\MSSQL$SQL2K_DB\Data\CUDProd_migr_log_1.ldf',
MOVE 'CUDProd_2_Log' To 'J:\MSData\MSSQL$SQL2K_DB\Data\CUDProd_migr_log.ldf'

Is there a way to do a restore without moving these files?

TIA,

Aaron B

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-09-29 : 12:11:45

You have to restore them. A database when restored will have all the same files that the DB did at time of backup.

Why do you have 3 log files on the production database anyway? Limited space on drives?

--
Gail Shaw
SQL Server MVP

sunfun
Starting Member

5 Posts

Posted - 2009-09-29 : 12:18:59

For several reasons. We don't have control over the drives and they are a little smaller than I am comfortable with them being. Occasionally the first one fills up and the transactions roll to the second drive and in the worst case scenario, the third drive. This usually only happens during our nightly maintenance window when we are reloading data from several other sources.

Ok, since I have to restore all the files, is there a way to detach and delete them after they are restored? Can I just use the sp_detach file then delete the file?

sunfun
Starting Member

5 Posts

Posted - 2009-09-29 : 12:38:55

Would it be easier for me to just detach both dbs then copy the .mdf file overwriting the MIGR one with the PROD one? If so, are there any gotchas I might run into? If I do detach and reattach the .mdf file, do I also HAVE to copy over the associated .ldf files? I wouldn't think so as I am truncating them prior to making the backup, but just want to be sure.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-09-29 : 14:03:20

quote:
Originally posted by sunfun Can I just use the sp_detach file then delete the file?

Only if you want a suspect and unusable database. Deleting a transaction log file is the fastest way wreck a database.

quote:
If I do detach and reattach the .mdf file, do I also HAVE to copy over the associated .ldf files?

Again, yes, unless you want to risk the DB been suspect (or recovery pending) upon attach. Just because you truncate the log doesn't mean you don't need the log. If the active portion of the log is in one of the files that you want to not copy/delete, the database will be unusable afterwards.

The way to get rid of files is to use ALTER DATABASE. If you get an error saying that the file is in use, then it means that the active portion of the log is in the file you're trying to drop.
I think that shrinkfile with the emptyfile option will work on logs, but not 100% sure as it's not something I've done before.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources