| Author |
Topic |
|
puja
Starting Member
18 Posts |
 Posted - 2005-09-21 : 13:02:55
|
| Hi, I am trying to backup the system db and I would like to know what would be the best way to do it? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-09-21 : 13:07:13
|
| BACKUP DATABASE masterTO DISK = 'E:\MSSQL\BACKUP\master.BAK'WITH INITBut perhaps you have a more specific question?Tara |
 |
|
|
puja
Starting Member
18 Posts |
Posted - 2005-09-21 : 13:35:08
|
| hi,Thanks for your reply. I am trying to use DATABASE Mainteance planand i wanted to know if this would be best way to go for copying the backup files?we don't have Transcational going on.so should I just use the database mainteance plan?I would like to follow the best procedure to copy the database. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-09-21 : 13:38:13
|
| The best practice is to not use database maintenance plans. Check out my set of DBA routines that are a great alternative to the maintenance plans:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspxCheck out isp_Backup in particular. You can tell it to backup the system databases only using the @dbType variable. Tara |
|
|
|
puja
Starting Member
18 Posts |
Posted - 2005-09-21 : 15:28:22
|
| hi, thanks for the reply. I have done the resource and some of the articles talks about backing the database using Database Maintenance tool or usr enterprise backup database tool. I am trying to reach the goal where I don't have to do backup transcational log since we don't have it in our enviornment. also, I don't want to use all my db to grow too much. If I select the backup database by right clicking the database and select the [database complete], [overwrite to media]...would this option would not take too much space from my disk?hope you can answer my questions,thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-21 : 15:28:35
|
| "I am trying to use DATABASE Maintenance plan"I'm with Tara no not using them; but is there a specific problem that you are anxious about in using the Maintenance Plans for the system databases?For example, if your User Databases want to do Transaction Backups then there is a bit of an issue, but that can be overcome easily.Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-09-21 : 15:31:56
|
| You can't do transaction log backups on system databases anyway, so I'm not sure why you bring that up. Could you explain?Tara |
|
|
|
puja
Starting Member
18 Posts |
Posted - 2005-09-21 : 16:10:02
|
| I will also be doing the backup on User database too.I am trying to reach the goal which will not use my hard drive disk space. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-09-21 : 16:22:14
|
| If your databases are set to SIMPLE recovery model which they should be in non-prod environments also on prod environments where you don't care about point in time recovery, then you just need to run full backups. If you require point in time recovery, then the best option is to run the tlog backups often. We run ours every 15 minutes.Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-21 : 16:41:10
|
| "You can't do transaction log backups on system databases anyway ... Could you explain?"There are some issues with Maintenance Plan Wizard where if you set up a backup plan that includes transaction backups, but you include databases that are set to SIMPLE, that it fails [when it tries to make the TLog backup]. This is a PITA because it would be nice to use the Wizard to back up "All databases" to ensure that any new databases created in the future were not overlooked.The result of this failure is that the backups are created, but the delete of the old backups doesn't get run (its possible the Wizard is processing the databases in order, so it might be possible for it to fail to backup too - e.g. if there are databases "after" the ones set to Simple, based on whatever OrderBy the Wizard is using, but I haven't seen that happen)Anyway, the upshot of this is that the TLog backups don't get deleted and one day the disk will be full :-(Kristen |
|
|
|
|
best way to backup the system db