October 24, 2011 at 9:33 am
We are running SQL Server 2005 SP3 on Windows 2003 Server. I know in Oracle you can shutdown the database and copy the datafiles to a backup folder. How can you accomplish this in SQL Server (and schedule the job). I believe the SQLSERVERAGENT Service requires the MSSQLSERVER Service. Is there a method to perform the following tasks and schedule it:
Stop SQL Server
Copy datafiles
Start SQL Server
OR
Close the database
Take database offline
Copy datafiles
Open database
Put database online
How would you schedule something like this?
Thanks, Kevin
October 24, 2011 at 9:52 am
why go via Oracle way when you can take online backups? Backup processes do not interfare with another user processes.
October 24, 2011 at 9:54 am
kevinsql7 (10/24/2011)
How would you schedule something like this?
Why do you want to?
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
October 24, 2011 at 9:57 am
You don't need to take your instance nor db offline to create valid sqlserver backups.
Have a look at "backup database" in books online.
Keep in mind you also need "backup log" if your db is in "bluk recovery" or "full" recovery model.
(have a look at Managing Transaction Logs By Gail Shaw[/url]
You can schedule these commands using sqlagent jobs.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 24, 2011 at 10:14 am
I would absolutely advocate against this with every breath I took, but yes, you could shut down the server, then copy the database and log files, then restart the server. It's a crazy, crazy way to get the job done, but it could work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 24, 2011 at 11:00 am
You'd have to do it outside the scope of SQL Server, in the Windows scheduler or some third-party task scheduler.
You can, via the command-line, issue a service-stop for the SQL service, then use robocopy, xcopy, or even just copy, to copy files around, then issue a service-start via the command-line. If you don't want to do it via command-line, pick your programming language of choice that can issue system commands to Windows (I know VB.NET can do this).
(Command line service start/stop reference: http://technet.microsoft.com/en-us/library/cc736564(WS.10).aspx)
HOWEVER, as already pointed out, just because you can doesn't mean you should. It's unnecessary, and less likely to result in a usable backup than just issuing a backup command from within SQL Server. It also doesn't result in anything you can use for point-in-time restore, unlike regular full + (possibly) diff + log backups, which is a potentially severe drawback.
- 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
October 30, 2011 at 4:56 pm
Hi,
Yes you can perform the activities(but it is your own risk ->you need to ensure many factors from your side
why you are planning for cold backup,downtime, etc...).
you can schedule task using windows task scheduler with windows login(ensure that if login deleted or password expires then the job is going to fail).
@echo off
REM
REM COMMENTS -- this is the cold backup job for SQL2000 Databases
REM
REM step 1 -- shutdown MSSQL2000 database and services
REM step 2 -- copy all MSSQL2000 files from
REM each drive to backup area
REM step 3 -- start MSSQL2000 database and services
REM
REM NOTE -- execution command line should be
REM
REM <Drive Name>:\sqlback\COLD\backup_sql.bat > <Drive Name>:\sqlback\COLD\backup_sql.log ----Here you will save this script in to backup_sql.bat
REM
REM *********************************************************
REM *********************************************************
REM *********************************************************
REM *********************************************************
REM
echo ********** Backup the Sql2000 Database *************************
echo *********************************************************
date /t
time /t
echo ******
echo ****** Stopping Sql Services
net stop sqlserveragent
net stop mssqlserver
echo ****** Sql Services Stopped
echo ******
echo ********** Copy all files to cold backup fromone drive to another *****************
xcopy <SourceDrive Name>:\mssql\data\*.* <Dest Drive Name>:\sqlback\coldback\d_drive\mssql\data\*.* /s/e/y
echo *********************************************************
date /t
time /t
echo ******
echo ****** Starting Sql Services
net start mssqlserver
net start sqlserveragent
echo ****** Sql Services Started
echo ******
echo **********************************************************
echo **********************************************************
Best Regards,
Rama Udaya.K
My Blog :ramaudaya.blogspot.com
October 30, 2011 at 6:12 pm
The post above explains how to do it, but as a few people have said, there's no need.
SQL Server does not need cold backups. Ever. The online backup is rock solid and data will be consistent.
October 31, 2011 at 6:34 am
The only time I ever do this is before and after applying a service pack or CU as like a safety net. That way should the master db ever become corrupt and SQL Server is not able to start you can quickly copy back the master mdf / ldf to get SQL Server up and running and then restore the db from a current backup after that to get it current.
October 31, 2011 at 6:38 am
Markus (10/31/2011)
The only time I ever do this is before and after applying a service pack or CU as like a safety net. That way should the master db ever become corrupt and SQL Server is not able to start you can quickly copy back the master mdf / ldf to get SQL Server up and running and then restore the db from a current backup after that to get it current.
Isn't that what a drive image is for?
Undoes EVERYTHING from a faulty patch, at every level.
- 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
November 15, 2011 at 2:47 pm
There is very little to no use for cold backup, either at SQL Server or Oracle world. The only time you might consider for example, is you are already taking database off service (database offline or SQLServer service stopped, for the reason upgrade for example). You could take cold backup right there. For hot backup, database has to be online and open.
Jason
http://dbace.us
😛
November 18, 2011 at 4:59 am
I agree with all the people have already replied there is no need to take a database ofline to do a backup. But if you really want to do this, then you can package the following into a SQL Agent job...
ALTER DATABASE xxxx SET OFFLINE
XCOPY xxxx.mdf
XCOPY xxxx.ldf
ALTER DATABASE xxxx SET ONLINE
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply