September 22, 2004 at 8:14 am
Hi
I just had to move backup from one disk to another because the backup files, mdf and ldf, use all disk space, today I had just 9.95 MB free, and thus our scheduled night job ended abnormaly.
When I now bakcup I am abit currious, by default the backup is set to append to existing, but what happends if I change this to overwrite? Recomended or not?
Furthermore, I see that our tempdb uses alot of free space. Have scheduled this to shrink right before night job starts, but can it also shrink while this job runs? Does it cause any problem for the job?
I have also a third problem...
I have moved one database from one server to another by copying the mdf and then use restore option in enterprise manager. But this does not work, I get a message saying that there is some consistency problem. Both original mdf file and the copy on the new server have the same size... Tip?
I hope that somebody can provide some tip on one or more of my issues.
Thank you for your time and help
Best regards
Dan
September 22, 2004 at 8:44 am
I can answer a couple.
Tempdb shrink should not be too much of a problem assuming that you have fast disks. Use statistics io to monitor.
For moving your db, try detaching and attaching your database instead of restoring. This should be a reasonable way to handle this.
as far as backups, I do not do appends, so I can't help you on that one.
September 22, 2004 at 9:22 am
1. You could give our product, MiniSQLBackup, a try. It can generate smaller backups for you.
2. If you set to overwrite, that means you will only have the latest backup. If you consistently copy this backup file to another location/server and tag them accordingly, it should be fine.
3. tempdb grows usually because of temporary workspace needed for sorts / temporary tables. Since tempdb does not permanently hold data pertaining to live databases, it should not affect your backup job.
4. There are 2 ways to move a database:
- detach, copy, attach
- backup, restore
Never, ever, ever, stop the SQL Server service, copy and try to attach. It does not work. You could make it work with unsupported functions, but that's something you would want to avoid.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
September 22, 2004 at 1:39 pm
Hi
Thank you for the help.
I willtru detach attach instead of restore,hope that helps.
I will also set tempdb to shrink a couple of times during night job.
Again, thank you.
Regards,
Dan
September 22, 2004 at 1:41 pm
As stated, you can't simply copy the MDF files across you have to copy the mdf and ldf files and then attach them to the db. My preferred method to duplicate databases is to simply do a backup and restore.
Shrinking the tempdb should not be an issue.
Be careful if you change a backup to overwrite. If you keep all of your backups in one archive file than changing the backup to initialize the backup will delete all of your old backups. I usually create a unique filename for all of my backups so I do not run into this however if you overwrite an archived backup file be prepared to lose all backups in that file.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply