August 28, 2008 at 12:38 am
Hi - I'm one of those "involuntary DBA's" at my company and am trying to learn the general concepts of databases and db administration at the same time.
I support an application that runs on MSDE and SQL Server 2000/05/Express. My question is this: Is it necessary to backup all files in the Data folder (i.e. Master, Model, etc.) or is it safe to backup just our application's db (which consists of one mdf and one ldf file)?
Keep in mind any worst case scenario where, after rebuilding a server and installing the same instance name, we would basically drop in the two files after stopping the service. Does this corrupt the new master MDF file in any way? Should I be running any procedures to synch the master db with the restored application db?
The db size is relatively small (under 2GB) so we are advising our customers to stop the service and backup the two files every night.
Thank you in advance,
Sid.
-Sidster
"You can have it done cheap, done well, or done fast. Pick two."
August 28, 2008 at 6:46 am
You should definitely backup your system database (master, model, msdb).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 28, 2008 at 6:49 am
ss1011 (8/28/2008)
we would basically drop in the two files after stopping the service.
its not quite as simple as that
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 28, 2008 at 5:54 pm
tempdb is the only db you don't need to backup. It's always better to back up too much & too often (within reason of course) ....... but "Murphy" is always on the lookout for those without CYA implementation.
August 28, 2008 at 7:17 pm
As a good disaster recovery option, you should always do a full backup of the system dbs at an interval that your organization is comfortable with, preferably on a daily basis considering the size of the system dbs are not that significant.
Hope this helps.
August 28, 2008 at 10:43 pm
THank you for all of your replies.
Can I trouble someone to explain why? I don't need exact details - perhaps just an overview of what's in the tables that might cause problems if not backed up.
Sid.
-Sidster
"You can have it done cheap, done well, or done fast. Pick two."
August 29, 2008 at 1:33 am
Master contains all the information on the logins on the SQL server, as well as the database references, the linked servers, server-level triggers and server settings.
Model is used to create new databases, including TempDB
MSDB contains all jobs, job histories, backup histories, DTS packages, SSIS packages (if stored in SQL) and someother related information.
Also, as I mentioned over a SQLTeam, you should be doing SQL backups (backup database...) or using the backup database maintenance plan and then copying those backup files to tape/backup storage, not stopping SQL and backing up the files.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 29, 2008 at 8:41 am
ss1011 (8/28/2008)
Can I trouble someone to explain why? I don't need exact details - perhaps just an overview of what's in the tables that might cause problems if not backed up.
Let me break the news for you... tables contain "data".
Lets say "data" is a pretty valuable and unique substance your company needs like you need oxigen -assuming you are not a alien.
If you do not backup the tables and the tables are lost... "data" is lost forever and that might make your bosses really angry 😉 ... they may believe they have to conduct human sacrifices to calm down the 'Creator of all Data" and believe me, this is not good news for you.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 29, 2008 at 9:09 am
Good advice above. Best reason to do system dbs? The server crashes and you need to restore elsewhere. You need master and potentially msdb to do this.
If you do that, you might need sp_change_users_login to sync paster with the app db.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 2, 2008 at 11:18 am
Thanks everyone for the feedback.
Our application is proprietary. The default DB is in MSDE and we use an application role to access the data. The sa password is not disclosed to our customers. Customers are encouraged to migrate out of MSDE to implement their own backup plans, however, I want to make sure that we're giving good advice to those that rely on our MSDE installation.
This is why we recommend cold backups. We also recommend a scheduler to start/stop our default instance. But I suppose I can look into an OSQL (via an EXE since we have to conceal the password) process to automate this.
-Sidster
"You can have it done cheap, done well, or done fast. Pick two."
September 2, 2008 at 11:31 am
Rather have a scheduled task that connects to the instance via windows authentication and runs the backup database task. Or create a login that only have permission to backup the database (db_backupoperator in each DB) and use that to backup the databases.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 2, 2008 at 11:40 am
ss1011 (9/2/2008)
Thanks everyone for the feedback....This is why we recommend cold backups. We also recommend a scheduler to start/stop our default instance. But I suppose I can look into an OSQL (via an EXE since we have to conceal the password) process to automate this...
Cold backups are not the way to backup SQL Server.
If you are not doing database backups using the SQL Server BACKUP command, you cannot really guarantee the ability to restore.
September 2, 2008 at 11:43 am
Points noted - thank you Michael and Gail.
-Sidster
"You can have it done cheap, done well, or done fast. Pick two."
September 2, 2008 at 2:46 pm
This is why we recommend cold backups. We also recommend a scheduler to start/stop our default instance. But I suppose I can look into an OSQL (via an EXE since we have to conceal the password) process to automate this.
The other option is to create user who can only do backups. One of the benefits of this is the backup user id and password can be given out as this is the only function that can be used. The user can also schedule the backups using their maintenance server or put in the config of your .NET app.
url=http://msdn.microsoft.com/en-us/library/ms189612(SQL.90).aspx for more info
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply