February 10, 2009 at 7:59 am
I've restoring a db as part of a testing process
>first the backup
BACKUP DATABASE [MY_DB] TO [MY_DB_backup] WITH FORMAT,
NAME = N'MY_DB-Full Database Backup', STATS = 10
GO
>then the restore it over the existing test env as such:
RESTORE DATABASE [MY_DB] FROM [MY_DB_BACKP] WITH RESTRICTED_USER,
FILE = 1,
MOVE N'PRISTINE' TO N'H:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MY_DB.mdf',
MOVE N'PRISTINE_log' TO N'H:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MY_DB_log.ldf',
NOUNLOAD, REPLACE, STATS = 10
GO
The thing that's odd is that the original db's datafile is 136g and the log is 112. When it's restored, the log file becomes 175g. What's causing this to happen? I don't need any of the transactions in the log file for this restored, testing copy. but I have allocated enough space to handle the full restore of both. Is there a way to restore it with a truncated log, or not have the log file as part of the backup (I know it needs a log file, but I don't need the recoverability on this test environment.
Thanks for any suggestions.
Bob
Not a downstroke, fistpicker
February 10, 2009 at 10:41 am
Though it sound strange that why the transaction log grew so much..
you can go ahead an truncate it
backup log databasename with truncate_only and
then shrink the database...
but if you want to do rootcause analysis then we have to try something else...
whats the recovery model of the database?
is it full or bulk logged?
February 10, 2009 at 10:53 am
Hi, you can also accomplish your task by detaching the db(sp_detach_db) and copying only the mdf file and going for a sp_attach_single_file_db.
Sriram
February 10, 2009 at 11:11 am
I don't think I can do the backup/truncate part. this is part of the production database and for reasons I can't detail here, I can't use our normal backup solution within this process. It's a sql server backup, but I can't truncate the log, due to the other , normal backups etc that are happening. This isn't a backup to restore back to itself, but a backup to move the database eslewhere. I cannot detatch and copy the mdf file.
Also, I know I can truncate the log file after restore, (after a bit of playing around. Seems it wants a transactional and log backup prior to the truncate), but there's not enough space on the drive for this grown log file. Drive is 300g, but the log file, which fits on the prod 300g drive, doesn't fit once restored.
Isn't there any way to specify a restore with a truncated log? Or a restore to just the data? Sounds unlikely. Recovery mode is full.
Bob
Not a downstroke, fistpicker
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply