January 3, 2011 at 7:13 am
Hi,
We're using SQL server 2005 SP2 and want to schedule a job that can automatically backup and restore DB at mid-night. We use sql agent schedule job and run the following backup and restore scripts:
DB backup scripts:
BACKUP DATABASE [MPServer] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\MPServer.bak' WITH NOFORMAT, INIT, NAME = N'MPServer-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM, CONTINUE_AFTER_ERROR
go
DB Restore script:
RESTORE DATABASE [MPServer] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\MPServer.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
go
Both above scripts would use master database to run.
However, some errors occasinally occur during DB restore job indicating the database is in use and cannot be restored and the job would fail. Here is the error detail:
Date 1/3/2011 3:24:47 PM
Log Job History (Restore)
Step ID 1
Server SITNACMAI52
Job Name Restore
Step Name Restore
Duration 00:00:00
Sql Severity 16
Sql Message ID 3013
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: SITNACMAI52\apvcsadm. RESTORE cannot process database 'MPServer' because it is in use by this session. It is recommended that the master database be used when performing this operation. [SQLSTATE 42000] (Error 3102) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
Any options for the sql scripts that can force to restore and overwrite the cuurent db even there's still some connections connected to the db? If not then is it necessary to auto clean up all the connections before restore DB? Is there any sql scripts that can clean up all the connections for dedicated db? Can take db offline task can clean up all the connections? Thanks a lot.
Best regards,
Wallace
January 3, 2011 at 7:44 am
To restore a database successfully, there should not be any other connection to that DB.
Now to answer your questions:-
Any options for the sql scripts that can force to restore and overwrite the cuurent db even there's still some connections connected to the db?
NO
If not then is it necessary to auto clean up all the connections before restore DB?
YES
Is there any sql scripts that can clean up all the connections for dedicated db?
You have check all the active connections and kill each of them. You can write a script also or search on the web.
Can take db offline task can clean up all the connections?
YES
January 3, 2011 at 8:24 am
Hi Wallace,
You can query sysprocesses to check which session is currently active on that database and kill it.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
January 3, 2011 at 9:09 am
You can use a script like this to kill connections: http://www.sqlservercentral.com/scripts/Maintenance+and+Management/31833/
January 3, 2011 at 3:08 pm
Now I have to ask - why would you do this?
It really does not make any sense to backup the current database, then immediately restore over the existing database that you just backed up.
I could see this process if you were copying to a secondary copy (read only, reporting, etc...). Or, if you were restoring to another instance as a reporting instance. But, restoring over the existing system with the current backup - just doesn't make sense.
When I have to restore over an existing database that might have connections, I use the following:
ALTER DATABASE {database} SET OFFLINE WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE ...
This will disconnect any active connections to the database immediately, then start the restore operation. I only do this on test/dev/report systems and never on a production system.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 3, 2011 at 6:25 pm
Jeffrey Williams-493691 (1/3/2011)
Now I have to ask - why would you do this?It really does not make any sense to backup the current database, then immediately restore over the existing database that you just backed up.
I could see this process if you were copying to a secondary copy (read only, reporting, etc...). Or, if you were restoring to another instance as a reporting instance. But, restoring over the existing system with the current backup - just doesn't make sense.
When I have to restore over an existing database that might have connections, I use the following:
ALTER DATABASE {database} SET OFFLINE WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE ...
This will disconnect any active connections to the database immediately, then start the restore operation. I only do this on test/dev/report systems and never on a production system.
Since we want to backup the latest database and restore the yesterday database image for our training center(i.e. the latest data is not crucial), and this job is done by daily.
Thanks and regards,
Wallace
January 3, 2011 at 7:01 pm
So, you are restoring the backup to another system - not the same system.
It looked like you were backing up the database, and restoring it to the same system. At least, that is how I read it.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 4, 2011 at 3:24 am
I think you have the MPServer database, which, you are trying to restore, specified in the "Database" text box in the job step definition. That would run the job with a connection to the MPServer database. Change that database to something else like master.
November 17, 2011 at 9:22 am
Try this Way
USE db_name
GO
ALTER DATABASE db_Name SET SINGLE_USER WITH ROLLBACK AFTER 10 SECONDS -- you chagne value of Seconds
GO
Use master
Go
RESTORE DATABASE [db_name]
FROM DISK = N'File Path'
WITH FILE = 1,
Move N'db_data' To N'file Path'
Move N'db_log' To N'file path.ldf'
USE db_name
GO
ALTER DATABASE db_name SET MULTI_USER
GO
November 18, 2011 at 12:50 pm
As replayed earlier, you can change the database to single mode and then do your restore and change it back to multi mode, but the problem with that is, if your database that you are restoring is huge, then it is going to take sometime for the restore to complete, in which the database is going to be unusable during that time. I would suggest the following:
- Restore to a different database name
- Once restore is done to the second database, kill all the connections to the live database
- Followed by a quick rename of the database names (switch the live one with the one you just restored)
- Drop the old live database
This way your downtime for the users very minimal.
Thanks,
Fitsum
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply