May 3, 2016 at 8:42 am
Hello SQL community, I need some help with the following task that I have, as I'm not into SQL and I will really thank you for helping me, I need to copy and paste locally these 3 files:
master, model, msdb to the C drive as a backup, and the question is, if I have to stop the SQL and services or if it is possible to just copy and paste without touching anything. I won't need to do this from DB only Ctrl+c and Ctrl+v The services are running and it is a local DB SQL 2008 if you have any questions please let me know.
Helenka!:-)
May 3, 2016 at 8:48 am
cookiemonstagt (5/3/2016)
Hello SQL community, I need some help with the following task that I have, as I'm not into SQL and I will really thank you for helping me, I need to copy and paste locally these 3 files:master, model, msdb to the C drive as a backup, and the question is, if I have to stop the SQL and services or if it is possible to just copy and paste without touching anything. I won't need to do this from DB only Ctrl+c and Ctrl+v The services are running and it is a local DB SQL 2008 if you have any questions please let me know.
Helenka!:-)
Not a good way to backup databases. Yes, you'd have to shut down SQL to do it. Best way, however, is to use SQL Server native backups to backup these system databases on a regular basis.
May 3, 2016 at 9:14 am
Thanks Lynn,
I have 2 questions
1) What is the best way to do this, as I see there are many services but only related to Monitoring SQL on this passive node.
2) Server is in cluster, the DB is local, each node has a stationery DB, Should I stop the services on both nodes? i will work on the passive node first.
May 3, 2016 at 9:17 am
The service will only be running on one node, that's the definition of active and passive nodes. That said, the system databases are on a shared drive, there's one copy of them, not one on each node, and hence you won't be working on the passive node at all, as it won't have access to the storage.
Why not ask your DBA to back the system databases up?
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
May 3, 2016 at 9:26 am
Ii know but I have no idea why, and that is my task unfortunately.
May 3, 2016 at 9:28 am
so my task is only to back up those 3 files manually, not sure why DB admin won't got this task but I wanted to know if in this case it is safety to do a copy and paste without touching anything ? 🙂 or what do you recommend me, thanks for the replies.
And I also have a question what is being saved on these 3 files if the DB is on a shared file? Sorry for my ignorance, thanks!
May 3, 2016 at 9:36 am
They're the system databases for the SQL Server instance. Without them, SQL is toast, it won't even start.
And you shouldn't be copy-pasting them. That's not how SQL backups are taken. They should be backed up from inside SQL Server, using SQL's native backup process.
And no, you can't copy-paste them without touching anything. SQL has the files locked exclusively while it's running.
I recommend that you speak to both the person who gave you this task, and to your DBA and get the person who knows database admin to do the database admin.
Someone who isn't DBA/sysadmin shouldn't even be to get access to those files if the server security is set up correctly.
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
May 3, 2016 at 10:01 am
Thanks I can try it but in case it wont be possible is it ok to just stop the db before the copy paste or it wont make any difference?
May 3, 2016 at 10:04 am
You can't stop a database. You'd need to stop the entire clustered database instance (and no, not from services either)
Please, get someone who knows what they're doing to do whatever this task is, before you accidentally break the entire server.
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
May 3, 2016 at 10:09 am
You can backup the dbs using the commands below from within SSMS or other appropriate SQL utility. Since someone has gone to the trouble to cluster the SQL instance, you certainly don't need or want to take SQL down just to take a backup.
BACKUP DATABASE master TO DISK = 'c:\your\full\path\to\backup\master.bak';
BACKUP DATABASE model TO DISK = 'c:\your\full\path\to\backup\msdb.bak';
BACKUP DATABASE msdb TO DISK = 'c:\your\full\path\to\backup\tempdb.bak';
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 3, 2016 at 10:14 am
ScottPletcher (5/3/2016)
You can backup the dbs using the commands below from within SSMS or other appropriate SQL utility. Since someone has gone to the trouble to cluster the SQL instance, you certainly don't need or want to take SQL down just to take a backup.
BACKUP DATABASE master TO DISK = 'c:\your\full\path\to\backup\master.bak';
BACKUP DATABASE model TO DISK = 'c:\your\full\path\to\backup\msdb.bak';
BACKUP DATABASE msdb TO DISK = 'c:\your\full\path\to\backup\tempdb.bak';
And that will work only if the OP has the necessary privileges to do it. Another reason to involve someone who knows what they are doing.
May 3, 2016 at 10:14 am
Oh my
.. thanks and one last question if the db is shared each node has these 3 files to be able only to run the instance on each node ? So these files are not usually modified everyday for example?
May 3, 2016 at 10:17 am
Can I run it using Msql studio mangment 2008 ?
May 3, 2016 at 10:17 am
cookiemonstagt (5/3/2016)
Oh my.. thanks and one last question if the db is shared each node has these 3 files to be able only to run the instance on each node ? So these files are not usually modified everyday for example?
No. There's one copy on shared storage that the active node uses. The instance can't run on multiple nodes at the same time.
The files are modified frequently, by SQL Server itself, some more often, some less.
And it's not three files. It's three databases, so at least 6 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
May 3, 2016 at 10:18 am
cookiemonstagt (5/3/2016)
Oh my.. thanks and one last question if the db is shared each node has these 3 files to be able only to run the instance on each node ? So these files are not usually modified everyday for example?
Depends on the level of activity, for instance, the master database is updated when ever logins are added, updated, or dropped; or configuration changes. If you have scheduled jobs running, msdb is updated as that is where SQL Server Agent database reside.
These databases should be backed up on a regular basis.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply