May 3, 2016 at 10:20 am
So why they need to bk up those files?
May 3, 2016 at 10:20 am
cookiemonstagt (5/3/2016)
So why they need to bk up those files?
They are system databases required by SQL Server to run correctly. Without them you have no server.
May 3, 2016 at 10:21 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?
Yes. Of course you must have the necessary permissions to run it. So try the BACKUP commands and see. If you can't run them, naturally you can notify whoever told you to do this that you don't have sufficient permissions to run backups on system dbs and you can all decide how to proceed from that point.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 3, 2016 at 10:24 am
Lynn Pettis (5/3/2016)
cookiemonstagt (5/3/2016)
So why they need to bk up those files?They are system databases required by SQL Server to run correctly. Without them you have no server.
So when the shared files are updated also the local will be updated at the
same time ..?
May 3, 2016 at 10:26 am
Lynn Pettis (5/3/2016)
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.
No particular knowledge required to run pre-coded backup commands. Using them in an actual recovery requires extensive knowledge, of course, but not just issuing basic BACKUP commands.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 3, 2016 at 10:26 am
So the data is saved on shared file and config locally .?
May 3, 2016 at 10:27 am
cookiemonstagt (5/3/2016)
Lynn Pettis (5/3/2016)
cookiemonstagt (5/3/2016)
So why they need to bk up those files?They are system databases required by SQL Server to run correctly. Without them you have no server.
So when the shared files are updated also the local will be updated at the
same time ..?
From what you have said, SQL Server is installed on a cluster, active/passive. There is no local copy of the system databases or even the user databases, they are on shared resources so there is only one copy of the databases.
May 3, 2016 at 10:29 am
cookiemonstagt (5/3/2016)
So the data is saved on shared file and config locally .?
I coded the backups to go to c:, which is local drive for that node only, because I thought that's what you wanted to do (perhaps I misunderstood). But you can easily change the backup file location to be a shared location that will be available to the active-previously-passive node when it starts up.
BACKUP DATABASE master TO DISK = 'S:\full\path\to\backup\file\master.bak'
where S: is a shared drive.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 3, 2016 at 10:31 am
ScottPletcher (5/3/2016)
cookiemonstagt (5/3/2016)
So the data is saved on shared file and config locally .?I coded the backups to go to c:, which is local drive for that node only, because I thought that's what you wanted to do (perhaps I misunderstood). But you can easily change the backup file location to be a shared location that will be available to the active-previously-passive node when it starts up.
BACKUP DATABASE master TO DISK = 'S:\full\path\to\backup\file\master.bak'
where S: is a shared drive.
Walking away ..........
May 3, 2016 at 10:38 am
cookiemonstagt (5/3/2016)
Lynn Pettis (5/3/2016)
cookiemonstagt (5/3/2016)
So why they need to bk up those files?They are system databases required by SQL Server to run correctly. Without them you have no server.
So when the shared files are updated also the local will be updated at the
same time ..?
There are no local files.
There is a single copy of each of these databases' files (6 minimum) on shared storage (since this is a cluster). They're essential for SQL Server to run, and hence they MUST be backed up. However a copy-paste is not a backup and is not what should be done. The DBA should be backing these up regularly as part of his regular scheduled database backups.
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
Viewing 10 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply