July 25, 2011 at 8:49 am
Hi folks,
I plan to do my backup this way:
1. stop server
2. copy all mdf/ldf to another place
3. restart server
Is it the best and safest way to run a reliable backup? will the maintenance jobs be backed up as well? where are those maintenance jobs stored in SQL 2005?
Thank you for you input.
July 25, 2011 at 9:15 am
I am not sure why you want to do this. You can just take back ups. On SSMS, you can use the maintenance plan to set up Back up of DB. But just because you took a back up does not mean that it is safe. You have to make sure that the Back up is valid and not corrupted. The best way to do it is to restore it to another server.
In the maintenance plan, you can set up daily Job for back up and also log back up (Once ever 1 hr or any other interval depending on your SLA). If you can apply these log back ups to another server, then you have a Standby server as well. This will help you in disaster recovery.
All jobs are stored in MSDB.
-Roy
July 25, 2011 at 9:22 am
Roy Ernest (7/25/2011)
I am not sure why you want to do this. You can just take back ups. On SSMS, you can use the maintenance plan to set up Back up of DB. But just because you took a back up does not mean that it is safe. You have to make sure that the Back up is valid and not corrupted. The best way to do it is to restore it to another server.In the maintenance plan, you can set up daily Job for back up and also log back up (Once ever 1 hr or any other interval depending on your SLA). If you can apply these log back ups to another server, then you have a Standby server as well. This will help you in disaster recovery.
All jobs are stored in MSDB.
SSMS is not reliable and it's very buggy
July 25, 2011 at 9:26 am
halifaxdal (7/25/2011)
Roy Ernest (7/25/2011)
I am not sure why you want to do this. You can just take back ups. On SSMS, you can use the maintenance plan to set up Back up of DB. But just because you took a back up does not mean that it is safe. You have to make sure that the Back up is valid and not corrupted. The best way to do it is to restore it to another server.In the maintenance plan, you can set up daily Job for back up and also log back up (Once ever 1 hr or any other interval depending on your SLA). If you can apply these log back ups to another server, then you have a Standby server as well. This will help you in disaster recovery.
All jobs are stored in MSDB.
SSMS is not reliable and it's very buggy
err NO.
Maintenance plans used tobe buggy. But it still works better than copying files manually.
This is the best AFAIK.
July 25, 2011 at 9:27 am
This is the first time I have anyone saying that. SSMS is just a GUI. It does not do any work. You use it to set up the Job. After that the job is run by the SQL Server Agent.
When you have to copy the mdf and ldf file, you have to stop the SQL Server. That means you will kill any connection that is running on SQL Server. Not the best method.
-Roy
July 25, 2011 at 9:31 am
halifaxdal (7/25/2011)
SSMS is not reliable and it's very buggy
What sort of problems are you having with it? If you post detailed problem descriptions / error messages, we might be able to assist you more.
July 25, 2011 at 9:32 am
Ninja's_RGR'us (7/25/2011)
halifaxdal (7/25/2011)
Roy Ernest (7/25/2011)
I am not sure why you want to do this. You can just take back ups. On SSMS, you can use the maintenance plan to set up Back up of DB. But just because you took a back up does not mean that it is safe. You have to make sure that the Back up is valid and not corrupted. The best way to do it is to restore it to another server.In the maintenance plan, you can set up daily Job for back up and also log back up (Once ever 1 hr or any other interval depending on your SLA). If you can apply these log back ups to another server, then you have a Standby server as well. This will help you in disaster recovery.
All jobs are stored in MSDB.
SSMS is not reliable and it's very buggy
err NO.
Maintenance plans used tobe buggy. But it still works better than copying files manually.
This is the best AFAIK.
You are right, the server needs to be made offline for a while, this approach applies to only small server/DB.
BTW: no one has answered my question, where is the job stored in SQL?
July 25, 2011 at 9:33 am
Brandie Tarvin (7/25/2011)
halifaxdal (7/25/2011)
SSMS is not reliable and it's very buggyWhat sort of problems are you having with it? If you post detailed problem descriptions / error messages, we might be able to assist you more.
Thank you, too many trivial things, fixable but annoying
July 25, 2011 at 9:33 am
msdb
July 25, 2011 at 9:33 am
halifaxdal (7/25/2011)
Roy Ernest (7/25/2011)
I am not sure why you want to do this. You can just take back ups. On SSMS, you can use the maintenance plan to set up Back up of DB. But just because you took a back up does not mean that it is safe. You have to make sure that the Back up is valid and not corrupted. The best way to do it is to restore it to another server.In the maintenance plan, you can set up daily Job for back up and also log back up (Once ever 1 hr or any other interval depending on your SLA). If you can apply these log back ups to another server, then you have a Standby server as well. This will help you in disaster recovery.
All jobs are stored in MSDB.
SSMS is not reliable and it's very buggy
I've been doing all my backups through SSIS jobs scheduled through SSMS. It has been reliable and I haven't encountered any bugs. We just finished testing the restores and everything was fine.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
July 25, 2011 at 9:34 am
There's just no reason to shutdown the server to take a backup.
The only even remotely possible way it makes sense if when you migrate databases from server a to b. And even then it's better to have them both online in case you need to revert to the old server.
July 25, 2011 at 9:38 am
I have been doing backups with SSMS, or scheduling them with SSMS for years, and haven't had issues with bugs.
There is no reason to stop the SQL Server service or take a database offline to run a backup. Backups are fully transactionally consistent with the time in which they run, regardless of activity on the database.
July 25, 2011 at 9:47 am
If you don't like running them through SSMS for whatever reason, you can run them through the command line. That'll allow you to keep the databases online and the service running, and will be more reliable than copying files.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 25, 2011 at 9:48 am
Ninja's_RGR'us (7/25/2011)
There's just no reason to shutdown the server to take a backup.
... unless you're working with Oracle. :Whistling:
It's called "cold backup" and it's advertised as "the only consistant backup". Strange, isn't it?
halifaxdal, if you have an Oracle background, forget everything you know about backups: it's a totally different story with SQL Server.
-- Gianluca Sartori
July 25, 2011 at 9:55 am
halifaxdal (7/25/2011)
Hi folks,I plan to do my backup this way:
1. stop server
2. copy all mdf/ldf to another place
3. restart server
Is it the best and safest way to run a reliable backup? will the maintenance jobs be backed up as well? where are those maintenance jobs stored in SQL 2005?
Thank you for you input.
Are these production databases ? If so, they should be in FULL recovery mode, so that you can also take regular (every 15-30 min) transaction log backups. Then you will need a proper backup (BACKUP DATABASE) to restore your transaction logs in case of disaster or data recovery. Copying mdf/ldf is not an adequate solution for production database backups.
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply