January 15, 2003 at 7:36 am
Hi,
We currently have a Sql Server 2000 production database which is around 72G. We go through a process to backup the database to the harddrive and restore it to our development environment. Unfortunately, we receive a 112 Error when we try to restore the database.
One problem that we have noticed is that we have a large Log file on the production server that is over 50% empty. Would this effect the size of the backup and if so what should we do to correct it.
Can we restore the database without the Log file? What is the process to do this?
or
Should we try to shrink the database on the production server? How will this impact the users?
Also, could we use DTS to move it without too much impact on the production server?
Thanks
Patrick
January 15, 2003 at 8:17 am
How much free space do you have in DEV server? How large the transaction log file and How many data files in Production server.
You may shrink the tracsation log file in Production server first and perform a backup.
If you have multiple data files, during the restoration the backup into DEV servre, you can re-arrange the data files into different disk drives.
January 15, 2003 at 11:31 am
Truncate the lo (with backup log DBNAME with Truncate_only) then ahrink the log File and then backup the database. The backup files should be smaller
January 15, 2003 at 12:33 pm
quote:
Can we restore the database without the Log file? What is the process to do this?
Yes, but it will require either detaching the DB or stopping SQL server. SQL will lock the file for use which will prevent copying is why. But if you run sp_detach_db to diconnect from SQL you can copy the .mdb file over to the other server and use sp_attach_db to reattach to the main server. Then on the other server use sp_attach_single_file_db to attach the .mdb and it will create a new log.
quote:
Should we try to shrink the database on the production server? How will this impact the users?
See previous, however using shrinkdb will cause period areas to lock and slow response time for users.
quote:
Also, could we use DTS to move it without too much impact on the production server?
Of course but depending on hom much data it may take awhile and logging of inserts could grow the new TL a bit and if issue is not enough drive space cause it not to work.
One additional note on previous I would backup the DB before doing a truncate on the log and shirnking just in case of issue. If you do it to a local drive is not to bad on speed. The backup file may not be smaller but the fact you do a shirnk on the log will mean the restore will not be as large. The way it works is the data is stored in the backup file and the file size of all the DB related files, so when restoring you will have the same size file restored as the original. If you have empty space it means it will also have empty space. Shirnking the file without truncating the TL will still produce roughly the same size backup but of course the resotre will not resotre to the larger size but the size as taken at backup.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply