April 16, 2010 at 4:35 am
Hello.
We have some issues with a sql 2005 server workgroup edition.
We want to stop the mssqlservice when the backup runs, but an microsoft support engineer told
us that doing this would result in "no back up of the SQL Server database information".
What does this mean?
Whould this be a problem for us under a recovery if the server crashes?
Best Regards
Håkon Galstad
April 16, 2010 at 2:25 pm
Database backups are separate from filesystem backups.
If you want the database to be backed up you need to use a backup designed for use in SQL server, which requires SQL Services to be running.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 16, 2010 at 2:34 pm
May I suggest that you take some time to read about backups and restores in Books Online, the SQL Server Help System? If, after reading, you still have questions we would be more than willing to help you with anything you don't understand.
April 29, 2010 at 6:50 am
Hello.
I have read a little but im still unsure.
I also asked Microsoft SQL support Engineer the following questions:
Question 1 - What do you think about the following solution:
1 - If we stop the sqlservice with the following command: net stop /y mssqlserver , would all the information in the log files be written to the database. Is this correct?
2 - The next step would be to run the SBS Backup jobb that includes the SQL database folder to internal backup disk.
3 - The last step would be to run this script command to start the sql services again:
net start mssqlserver
net start sqlserveragent
Microsoft Answere 1 - I am not sure if your solution is OK. you may need to ask backup support engineer to ask if it OK. From sql server aspect, it is not recommended.
Question 2 - You mention that stopping the SQL service the database information would not be backed up, what is the content of database information exactly and what do we need it for?
Microsoft Answere 2 - The Microsoft Engineer did not have a good answere to this.
We hope you can help us with this questions.
Haakon
April 29, 2010 at 7:38 am
Cold backups (when the service is stopped) are not recommended for SQL Server. Take database backups (BACKUP DATABASE) and copy those to tape/offsite storage.
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
April 29, 2010 at 8:05 am
Hello Gail.
Thank you for youre answer.
Our trouble is that we have a application witch generates a lot ammount of databases. We are now past about 2700 databases.
This causes normal SQL backups to fail, and Microsoft support Egnineers have not been able to solve the issue.
Because of this we have to find another solution.
Do we lose anything by doing cold backups?
Why is this not recommended?
We are thankfull for any information related to this.
\Haakon
April 29, 2010 at 8:10 am
Lots of DBs cause backups to fail? With what error?
How were you doing those backups? Maintenance plan?
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
April 29, 2010 at 10:37 am
haakon 29117 (4/29/2010)
1 - If we stop the sqlservice with the following command: net stop /y mssqlserver , would all the information in the log files be written to the database. Is this correct?2 - The next step would be to run the SBS Backup jobb that includes the SQL database folder to internal backup disk.
SET @dramaqueen = ON
"AH! No! Make the pain stop!"
SET @dramaqueen = OFF
Bad, bad, bad idea to take backups like this. You should be using your backup software to copy the SQL Server *BACKUP* files, not the data or log files. Taking a database on and offline like this interfers with normal SQL operations and, on a bad day, could potentially cause data corruption and keep the database from coming back online properly.
Please don't do this. I beg you. SQL Server should be backing up to a folder separate from the data/log files and you should be using your file backup software to back up that folder.
April 29, 2010 at 10:42 am
haakon 29117 (4/29/2010)
Our trouble is that we have a application witch generates a lot ammount of databases. We are now past about 2700 databases.
Please tell me what this application is so I can avoid using it. No application, in my mind, should ever be able to self-spawn entire databases.
haakon 29117 (4/29/2010)
This causes normal SQL backups to fail, and Microsoft support Egnineers have not been able to solve the issue.
Normal SQL Backups? There are several different ways of doing SQL Backups. 1) Through T-SQL, 2) Through a maint. Plan, 3) Through SSIS, 4) Through third party software like Litespeed. Please identify which method you are using.
As Gail said, please post details on the errors.
Also, it would be helpful to know where the backups are going (local drive, SAN, NAS, or UNC path), how much space is available on that path compared to the size of the backups, and what types of backups you're doing.
April 29, 2010 at 10:45 am
Brandie Tarvin (4/29/2010)
haakon 29117 (4/29/2010)
Our trouble is that we have a application witch generates a lot ammount of databases. We are now past about 2700 databases.Please tell me what this application is so I can avoid using it. No application, in my mind, should ever be able to self-spawn entire databases.
MS CRM is one such application that does this. It permits end users to create new organizations (it can also be scheduled to do it automatically) - which in turn spawns a new database.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 29, 2010 at 10:50 am
CirquedeSQLeil (4/29/2010)
MS CRM is one such application that does this. It permits end users to create new organizations (it can also be scheduled to do it automatically) - which in turn spawns a new database.
What is an organization? (By the terms of MS CRM, that is).
April 29, 2010 at 10:52 am
CirquedeSQLeil (4/29/2010)
Brandie Tarvin (4/29/2010)
haakon 29117 (4/29/2010)
Our trouble is that we have a application witch generates a lot ammount of databases. We are now past about 2700 databases.Please tell me what this application is so I can avoid using it. No application, in my mind, should ever be able to self-spawn entire databases.
MS CRM is one such application that does this. It permits end users to create new organizations (it can also be scheduled to do it automatically) - which in turn spawns a new database.
SharePoint (MOSS) is another one. You can create multiple content databases.
April 29, 2010 at 10:55 am
Brandie Tarvin (4/29/2010)
CirquedeSQLeil (4/29/2010)
MS CRM is one such application that does this. It permits end users to create new organizations (it can also be scheduled to do it automatically) - which in turn spawns a new database.What is an organization? (By the terms of MS CRM, that is).
It should coincide with your business. An organization is an entity to establish a timeline difference or business rules differences. CRM doesn't support them being in the same database (by default setup). Thus if you had three clients all in crm - you would have 3 databases in addition to the crm config database. Each of these databases would hold different rules but be considered an organization.
The alternative method for that scenario is a redesign of workflows if possible.
I have seen some places that use the organization to delineate a financial accounting period. They would spawn a new database for each month if need be (I saw one that did a new database a day). Scary tactics.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 29, 2010 at 10:59 am
Lynn Pettis (4/29/2010)
SharePoint (MOSS) is another one. You can create multiple content databases.
I can actually see that being useful and not growing to monsterous proportions if it's admined correctly. In my workplace, IT has control over the Sharepoint stuff, so it's not like every user and their brother will be creating a new database every time they want a new toy.
But auto-spawning? Uncontrolled user spawning? I will have nightmares tonight about that one.
April 29, 2010 at 11:03 am
I would NOT recommend this, but how can stopping the service potentially cause data corruption? The service writes all data to disk. If you restart the server before all databases are backed up, I can see that you might get information written to/from master/msdb that wouldn't be captured to give you a definite point in time, but that could happen with SQL backups as well.
If the backup files are in use when SQL starts, the dbs will be suspect. But that's easy to recover from since it's just a file not found issue.
I think more this means downtime, and potentially issues with recovering in a disaster, but I wouldn't expect this results in corruption in any way.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply