September 9, 2004 at 12:32 pm
I am backing up master database and msdb in differentail mode> my master db size is 17.63 MB and my backup file is around 2gb.Its also the same with msdb. ANd one more question is that can i backup tempdb.
Thanks.
September 9, 2004 at 12:57 pm
No you can't back up tempdb. It is of no value anyway. Tempdb is temporary space needed by SQL Server. It gets recreated if you restart the server. The other system databases master, msdb, are set to a recovery model of simple. Do not change this. If your master is only 17.63 MB why bother with differential backups. Just do full backups. You won't save any time and recovery will be faster.
Francis
September 9, 2004 at 1:01 pm
What option should i select in this case(full backup of master and msdb)
1.Append to media
2.Overwrite existing media
September 9, 2004 at 1:05 pm
It depends. Append to media creates another backup stored in the same physical backup file. Overright media will delete the old backup and store your new one in its place.
Francis
September 10, 2004 at 3:55 am
Make sure you test restoring. You'll know then if your plans are worth while.
fhanlon is right. TempDB shouldn't be backed up. Nothing that's in there needs to be kept, and SQL Server deletes and recreates the database anyhow.
To restore a Differential, you need the Full backup that was performed before it. So if you overwrite your Full backup with a Differential, you then have NO BACKUP.
However. If you always append backup to the same file on disk, the backup file will keep growing. The disk backup should be regularly backed up to tape, and tested.
If you are using a Database Maintenance plan, you'll notice that it always creates a new file for each backup. It then deletes the files from disk after X days. I follow that idea, so you don't loose data, and if you use a timeserial in your filename then you know the order the backups were created in.
Julian Kuiters
juliankuiters.id.au
September 10, 2004 at 4:31 am
Hi,
If you need to backup tempdb then backup the model database as it is used as a template for creating the tempdb database when the server is started. The only time I need to backup the model database is when our stored procedures and temp tables use UDT's, just make sure the model database has the UDT's installed.
Regards
Richard...
September 10, 2004 at 7:30 am
In your original post, you seemed to have a question about the size of the master backup (but you post wasn't worded as a question - so it's hard to tell what you wanted to know).
You appear to be backing up a 17+MB master database, but the backup file is 2 GB. Is that correct? Or are you backing up ALL the system databases to one backup file (in which case the sum of all the database sizes should equal the backup size)?
If the backup of the master database is 2 GB and the actual size appears to be 17+MB, it might be due to unused space.
A database has N amount of space. Let's say it's 10 MB. You put 8 MB of data in it. The database size is still 10MB (8 MB data + 2 MB of unused space). The unused space is NOT "returned" to the operating system unless a SHRINKDATABASE or SHRINKFILE is done. Now let's say you add 10 more MB of data. Your db fills up the last 2 MB and grows more space for the rest of the data. Let's say you have autogrow set for 20MB. Now the database is 30 MB with 18 used and 12 unused. Now you delete some 5 MB of data. The database is still 30 MB, but with 13 MB of data and 17 MB of unused space.
Run the command sp_spaceused for your database and you'll see how much space is used by data, used by indexes, unused, reserved, etc.
-SQLBill
September 10, 2004 at 10:48 am
Also, what size did you orginally create those databases as? You may have created them as 2 GB databases. (In Enterprise Manager, right click on the database. Go to the Data File tab and Log File tab and see what the allocated sizes are).
-SQLBill
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply