T-SQL Script: restore

Showing posts with label restore. Show all posts

Friday, March 23, 2012

Scheduled Task to restore Database ?

There is a requirement to restore the production database to testing
database regularly. It can be done manually with no big problem except
changing the logins.
However, someone suggests scheduling a task to restore the production
database to testing database on every Sunday. Is it possible to do so ?
Thanking you in anticipation.Robert wrote:
> There is a requirement to restore the production database to testing
> database regularly. It can be done manually with no big problem except
> changing the logins.
> However, someone suggests scheduling a task to restore the production
> database to testing database on every Sunday. Is it possible to do so ?
> Thanking you in anticipation.
>
You can create a SQL script and then schedule this to run every Sunday.
It might also be necessary to add a step in the script to disconnect any
user sessions before you start the restore. Otherwise the Restore will
fail if there're users connected.
Regards
Steen|||Robert
Well , in our company we do it every month , so prior to RESTORE to
developing server we backup an existing database (on Developing Server) and
the drop it
Yes , you can create an job to perform it , however I do it by running
stored procedure that does restore operation from QA
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:%23dqYBjLIGHA.3144@.TK2MSFTNGP11.phx.gbl...
> There is a requirement to restore the production database to testing
> database regularly. It can be done manually with no big problem except
> changing the logins.
> However, someone suggests scheduling a task to restore the production
> database to testing database on every Sunday. Is it possible to do so ?
> Thanking you in anticipation.
>|||Dear Steen,
Do you have any idea where can I find samples of those scripts - disconnect
user and restore ?
Thanks
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:ugQw8oLIGHA.1424@.TK2MSFTNGP12.phx.gbl...
> Robert wrote:
>> There is a requirement to restore the production database to testing
>> database regularly. It can be done manually with no big problem except
>> changing the logins.
>> However, someone suggests scheduling a task to restore the production
>> database to testing database on every Sunday. Is it possible to do so ?
>> Thanking you in anticipation.
> You can create a SQL script and then schedule this to run every Sunday.
> It might also be necessary to add a step in the script to disconnect any
> user sessions before you start the restore. Otherwise the Restore will
> fail if there're users connected.
>
> Regards
> Steen|||> Do you have any idea where can I find samples of those scripts -
> disconnect user and restore ?
Below is an example (SQL 2000). See the Books Online for syntax details.
USE master
ALTER DATABASE MyTestDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
RESTORE DATABASE MyTestDatabase
FROM DISK='C:\Backups\MyProductionDatabase.bak'
WITH
MOVE 'MyProductionDatabase' TO 'E:\DataFiles\MyTestDatabase.mdf',
MOVE 'MyProductionDatabase_Log' TO 'F:\LogFiles\MyTestDatabase_Log.ldf'
--login/user fixup here
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:Okkt8YNIGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Dear Steen,
> Do you have any idea where can I find samples of those scripts -
> disconnect user and restore ?
> Thanks
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:ugQw8oLIGHA.1424@.TK2MSFTNGP12.phx.gbl...
>> Robert wrote:
>> There is a requirement to restore the production database to testing
>> database regularly. It can be done manually with no big problem except
>> changing the logins.
>> However, someone suggests scheduling a task to restore the production
>> database to testing database on every Sunday. Is it possible to do so ?
>> Thanking you in anticipation.
>> You can create a SQL script and then schedule this to run every Sunday.
>> It might also be necessary to add a step in the script to disconnect any
>> user sessions before you start the restore. Otherwise the Restore will
>> fail if there're users connected.
>>
>> Regards
>> Steen
>|||Dear Dan,
Thank you for your advice and it works properly.
I have set up a job to with 2 steps - The first one is to make the backup of
the Production DB and the second one is to restore to the Testing DB.
I would like to make 2 enhancement and would like to seek your advice.
1) When I "Set Single User", I find that if someone is connected, it fails.
Is it possible to disconnect users connected to the Testing Database ?
2) How can I delete the 'C:\Backups\MyProductionDatabase.bak' if I would
like to include it in Step 3 ?
Thanks again.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eELyPDOIGHA.2472@.TK2MSFTNGP10.phx.gbl...
>> Do you have any idea where can I find samples of those scripts -
>> disconnect user and restore ?
> Below is an example (SQL 2000). See the Books Online for syntax details.
> USE master
> ALTER DATABASE MyTestDatabase
> SET SINGLE_USER
> WITH ROLLBACK IMMEDIATE
> RESTORE DATABASE MyTestDatabase
> FROM DISK='C:\Backups\MyProductionDatabase.bak'
> WITH
> MOVE 'MyProductionDatabase' TO 'E:\DataFiles\MyTestDatabase.mdf',
> MOVE 'MyProductionDatabase_Log' TO 'F:\LogFiles\MyTestDatabase_Log.ldf'
> --login/user fixup here
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:Okkt8YNIGHA.3700@.TK2MSFTNGP15.phx.gbl...
>> Dear Steen,
>> Do you have any idea where can I find samples of those scripts -
>> disconnect user and restore ?
>> Thanks
>> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
>> news:ugQw8oLIGHA.1424@.TK2MSFTNGP12.phx.gbl...
>> Robert wrote:
>> There is a requirement to restore the production database to testing
>> database regularly. It can be done manually with no big problem except
>> changing the logins.
>> However, someone suggests scheduling a task to restore the production
>> database to testing database on every Sunday. Is it possible to do so
>> ?
>> Thanking you in anticipation.
>> You can create a SQL script and then schedule this to run every Sunday.
>> It might also be necessary to add a step in the script to disconnect any
>> user sessions before you start the restore. Otherwise the Restore will
>> fail if there're users connected.
>>
>> Regards
>> Steen
>>
>|||> 1) When I "Set Single User", I find that if someone is connected, it
> fails. Is it possible to disconnect users connected to the Testing
> Database ?
Did you also include the 'WITH ROLLBACK IMMEDIATE' option? That should kill
all connections to that database except your own (you can issue the command
from master). However, it might take a little time for the killed
transaction(s) to rollback. In that case, you might try including the
following between the ALTER DATABASE and RESTORE:
--wait for all database locks to be released
WHILE EXISTS
(
SELECT *
FROM syslocks
WHERE dbid = DB_ID('MyDatabase')
)
BEGIN
WAITFOR DELAY '00:00:01'
END
> 2) How can I delete the 'C:\Backups\MyProductionDatabase.bak' if I would
> like to include it in Step 3 ?
You can include the delete command (DEL
"C:\Backups\MyProductionDatabase.bak") in a CmdExec job step. You could
also delete the file from an ActiveX script or T-SQL xp_cmdshell command but
those methods are more complex than needed for this simple requirement.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:uB4al1YIGHA.1132@.TK2MSFTNGP10.phx.gbl...
> Dear Dan,
> Thank you for your advice and it works properly.
> I have set up a job to with 2 steps - The first one is to make the backup
> of the Production DB and the second one is to restore to the Testing DB.
> I would like to make 2 enhancement and would like to seek your advice.
> 1) When I "Set Single User", I find that if someone is connected, it
> fails. Is it possible to disconnect users connected to the Testing
> Database ?
> 2) How can I delete the 'C:\Backups\MyProductionDatabase.bak' if I would
> like to include it in Step 3 ?
> Thanks again.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eELyPDOIGHA.2472@.TK2MSFTNGP10.phx.gbl...
>> Do you have any idea where can I find samples of those scripts -
>> disconnect user and restore ?
>> Below is an example (SQL 2000). See the Books Online for syntax details.
>> USE master
>> ALTER DATABASE MyTestDatabase
>> SET SINGLE_USER
>> WITH ROLLBACK IMMEDIATE
>> RESTORE DATABASE MyTestDatabase
>> FROM DISK='C:\Backups\MyProductionDatabase.bak'
>> WITH
>> MOVE 'MyProductionDatabase' TO 'E:\DataFiles\MyTestDatabase.mdf',
>> MOVE 'MyProductionDatabase_Log' TO
>> 'F:\LogFiles\MyTestDatabase_Log.ldf'
>> --login/user fixup here
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Robert" <Robert@.discussions.microsoft.com> wrote in message
>> news:Okkt8YNIGHA.3700@.TK2MSFTNGP15.phx.gbl...
>> Dear Steen,
>> Do you have any idea where can I find samples of those scripts -
>> disconnect user and restore ?
>> Thanks
>> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
>> news:ugQw8oLIGHA.1424@.TK2MSFTNGP12.phx.gbl...
>> Robert wrote:
>> There is a requirement to restore the production database to testing
>> database regularly. It can be done manually with no big problem
>> except changing the logins.
>> However, someone suggests scheduling a task to restore the production
>> database to testing database on every Sunday. Is it possible to do so
>> ?
>> Thanking you in anticipation.
>> You can create a SQL script and then schedule this to run every Sunday.
>> It might also be necessary to add a step in the script to disconnect
>> any user sessions before you start the restore. Otherwise the Restore
>> will fail if there're users connected.
>>
>> Regards
>> Steen
>>
>>
>|||Dear Dan,
Thank you for your advice.
When I try to disconnect yesterday, maybe I have already kicked users out
but I am not aware. I still get the error message that it cannot be set to
single user - maybe because I am still connecting to it.
I will try your suggestion tomorrow.
Thanks for your advice again.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%235jXFoaIGHA.516@.TK2MSFTNGP15.phx.gbl...
>> 1) When I "Set Single User", I find that if someone is connected, it
>> fails. Is it possible to disconnect users connected to the Testing
>> Database ?
> Did you also include the 'WITH ROLLBACK IMMEDIATE' option? That should
> kill all connections to that database except your own (you can issue the
> command from master). However, it might take a little time for the killed
> transaction(s) to rollback. In that case, you might try including the
> following between the ALTER DATABASE and RESTORE:
> --wait for all database locks to be released
> WHILE EXISTS
> (
> SELECT *
> FROM syslocks
> WHERE dbid = DB_ID('MyDatabase')
> )
> BEGIN
> WAITFOR DELAY '00:00:01'
> END
>> 2) How can I delete the 'C:\Backups\MyProductionDatabase.bak' if I would
>> like to include it in Step 3 ?
> You can include the delete command (DEL
> "C:\Backups\MyProductionDatabase.bak") in a CmdExec job step. You could
> also delete the file from an ActiveX script or T-SQL xp_cmdshell command
> but those methods are more complex than needed for this simple
> requirement.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:uB4al1YIGHA.1132@.TK2MSFTNGP10.phx.gbl...
>> Dear Dan,
>> Thank you for your advice and it works properly.
>> I have set up a job to with 2 steps - The first one is to make the backup
>> of the Production DB and the second one is to restore to the Testing DB.
>> I would like to make 2 enhancement and would like to seek your advice.
>> 1) When I "Set Single User", I find that if someone is connected, it
>> fails. Is it possible to disconnect users connected to the Testing
>> Database ?
>> 2) How can I delete the 'C:\Backups\MyProductionDatabase.bak' if I would
>> like to include it in Step 3 ?
>> Thanks again.
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:eELyPDOIGHA.2472@.TK2MSFTNGP10.phx.gbl...
>> Do you have any idea where can I find samples of those scripts -
>> disconnect user and restore ?
>> Below is an example (SQL 2000). See the Books Online for syntax
>> details.
>> USE master
>> ALTER DATABASE MyTestDatabase
>> SET SINGLE_USER
>> WITH ROLLBACK IMMEDIATE
>> RESTORE DATABASE MyTestDatabase
>> FROM DISK='C:\Backups\MyProductionDatabase.bak'
>> WITH
>> MOVE 'MyProductionDatabase' TO 'E:\DataFiles\MyTestDatabase.mdf',
>> MOVE 'MyProductionDatabase_Log' TO
>> 'F:\LogFiles\MyTestDatabase_Log.ldf'
>> --login/user fixup here
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Robert" <Robert@.discussions.microsoft.com> wrote in message
>> news:Okkt8YNIGHA.3700@.TK2MSFTNGP15.phx.gbl...
>> Dear Steen,
>> Do you have any idea where can I find samples of those scripts -
>> disconnect user and restore ?
>> Thanks
>> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
>> news:ugQw8oLIGHA.1424@.TK2MSFTNGP12.phx.gbl...
>> Robert wrote:
>> There is a requirement to restore the production database to testing
>> database regularly. It can be done manually with no big problem
>> except changing the logins.
>> However, someone suggests scheduling a task to restore the production
>> database to testing database on every Sunday. Is it possible to do
>> so ?
>> Thanking you in anticipation.
>> You can create a SQL script and then schedule this to run every
>> Sunday.
>> It might also be necessary to add a step in the script to disconnect
>> any user sessions before you start the restore. Otherwise the Restore
>> will fail if there're users connected.
>>
>> Regards
>> Steen
>>
>>
>>
>

Scheduled Task to restore Database ?

Wednesday, March 21, 2012

Scheduled Restore DB in EM?

Dear All,
We all know that we can schedule backup DB & Log in EM using Database Maintenance Plan.
Is there a way we can schedule Restore DB using EM?
Anyone?
Thanks.Yup ... write the sql code to do so and schedule as a job.|||Thanks Enigma... seems like that's the only option.

rgds|||I presume you will know better if you read thru BOL as a first hand help.|||Thanks Satya... your prevision is true.

I managed to get the job done... writing the script and scheduled as job.

Scheduled jobs not repeating (log shipping restore)

I have several log shipping restore jobs that are scheduled to start at
the same time and repeat every 15 minutes. This has worked correctly
until last w

when some of the jobs would only run once. No errors
were produced and Agent said the jobs completed successfully, no reason
given as to why they failed to repeat.
Does anyone have any suggestions?
Thanks,
CurtIn order to try helping you I would need to know more details about what has
lead to the change in system's behavior. Did you add new jobs, did the
system go under heavy load, have you changed schedules manually etc. ?
It would also help if you look at agent's error log for some clues.
Ciprian Gerea
SDE, SqlServer
This posting is provided "AS IS" with no warranties, and confers no rights.
<curtmorrison@.yahoo.com> wrote in message
news:1145901222.247025.29230@.t31g2000cwb.googlegroups.com...
>I have several log shipping restore jobs that are scheduled to start at
> the same time and repeat every 15 minutes. This has worked correctly
> until last w

when some of the jobs would only run once. No errors
> were produced and Agent said the jobs completed successfully, no reason
> given as to why they failed to repeat.
> Does anyone have any suggestions?
> Thanks,
> Curt
>|||Is it possible that if the job is set the repeat every 15 minutes, but
the inital job takes over 2 hours to complete, the agent scheduler will
give up on it? The jobs that are repeating correctly only take about 10
minutes at most to complete, while all the jobs that are failing to
repeat are averaging about 1.5 hours.|||The scheduler does not give up on the job, but the job won't be executed if
it's already running. After the 2 hour time period elapses, does the
recurring job get rescheduled?
Ciprian Gerea
SDE, SqlServer
This posting is provided "AS IS" with no warranties, and confers no rights.
<curtmorrison@.yahoo.com> wrote in message
news:1145995810.372988.102280@.e56g2000cwe.googlegroups.com...
> Is it possible that if the job is set the repeat every 15 minutes, but
> the inital job takes over 2 hours to complete, the agent scheduler will
> give up on it? The jobs that are repeating correctly only take about 10
> minutes at most to complete, while all the jobs that are failing to
> repeat are averaging about 1.5 hours.
>|||After the 2 hour time period elapses, the recurring job doesn't run
again until the following day as scheduled.

Wednesday, March 7, 2012

Schedule Local Package to perform FTP and DB restore

Hi,

Did anyone successfully set up a local package to first ftp a db.bak
and second perform an automated db restore?

I need to perform an automated task, which ftp nightly backup file to
another server and then restore onto a database and leave the database
in read-only mode for additional transaction logs restore during the
day.

Can someone help and provide the procedures on how to do that?

Thanks in advance."xo55ox" <xo55ox@.hotmail.com> wrote in message
news:abbcb3d7.0405101048.1ce74ed6@.posting.google.c om...
> Hi,
> Did anyone successfully set up a local package to first ftp a db.bak
> and second perform an automated db restore?
> I need to perform an automated task, which ftp nightly backup file to
> another server and then restore onto a database and leave the database
> in read-only mode for additional transaction logs restore during the
> day.
> Can someone help and provide the procedures on how to do that?
> Thanks in advance.

You don't mention your MSSQL version or edition, but you might want to check
out this article about implementing log shipping with the SQL 2000 Standard
edition using a reskit tool:

http://www.winnetmag.com/Article/Ar...3231/23231.html

Or try these links:

http://sqlguy.home.comcast.net/logship.htm
http://www.sql-server-performance.c...og_shipping.asp

If you want to use DTS, it might be easier to execute the package on the
destination (standby) server, because the DTS FTP task can only GET, not
PUT. There is an alternative implementation that will do both here, by the
way:

http://www.sqldts.com/?302

To implement a log shipping package should be straightforward:

1 - Run BACKUP DATABASE on the source server (Execute SQL task)
2 - Use an FTP task to GET the backup file on to the destination server
3 - Run RESTORE DATABASE WITH STANDBY on the target server (Execute SQL
task)

But to add error handling, logging etc. can become more complicated, so you
may want to see if one of the existing solutions is good enough for you.

Simon

Tuesday, February 21, 2012

Schedule a scripted restore on 2005 Express from .bak file.

Hi i have an web app demo that allows users to add info and change attributes within a SQL 2005 Express DB. I'd like to restore a clean copy of this database every couple of hours from a .bak file using a Windows scheduled task on the server. Has anyone got a .sql script for database restoration that i could use and call using a .cmd script file? Thanks.

Hi TheGrox,

Generally speaking the sql restoration script is like this:

RESTORE DATABASE [Your_databasename] FROM DISK = N'File_Path\BCKUP.bak' WITH FILE = 1, NOUNLOAD, STATS = 10
GO

However, I don't think it can be used from a .cmd script file. To run .sql script file, you need to run it within sql server query analyzer (if you run it from a .cmd script, first it will jump up a window asking your to login to sql server, even though you enter the server name and user/password and login successfully, that sql script file will be opened there while won't be executed until you click the execute button from the GUI).

I think there are two solutions for you requirment. One solution is the easist, upgrade your sql express to a full vertion and use Server Agent-> Jobs. Run that script in a job and schedule the job to run at a sepcific time frequency. I believe it won't cost you more than 5 minutes;

The second solution is to use ado.net.Open your visual studio and create a new project, build a connection to your database first and then send the restore sqlcommand to it. Compile and build you project and after that, you create a windows schedule task and run that application regularly.

Hope my suggestion helps

|||

Hi, thanks for the suggestions. The running the .sql from a .cmd file isnt a problem, i already do that to automatically back up all DB's in my SQL Express instance by having a file called BackupDB.cmd with the following code:

CLS
ECHO OFF
ECHO Testing to make sure directories exist.
IF NOT EXIST C:\SQLBackups MD C:\SQLBackups

ECHO Complete with directory creation
ECHO **********************************************************
ECHO Backing up databases......
sqlcmd -S .\SQLEXPRESS -i c:\Windows\BackupExpress.sql -o C:\SQLbackups\backup.log
ECHO **********************************************************
ECHO Backup complete. Look in the C:\SQLBackups\Backup.log file for information.

ECHO ON

The BackupExpress.sql file is simply a script that loops through all databases on the server and creates a .bak file for each of them in the specified directory. Works nicely andmeans i dont need to reconfigure anything if a new DB is created, it is just automatically included in the backups.

So i guess i just need to create a Restore.sql script using the syntax you quoted and call it as above. The command runs under the context of a valid SQL user account specified when the scheduled task is created so login is not an issue.

The script for the BackupExpress.sql file if anyone is interested in using it is:

/**File Name: BackupExpress.sqlDescription: Backs up all databases. This script is mainly meant for SQL Express instancesThe script requires a C:\SQLBackups directory by default to backup to but can be changed with the @.OutputPath variable.Accompanying file is BackupExpress.cmd, which is used to schedule the script.**/SET QUOTED_IDENTIFIEROFF USE masterGOSET NOCOUNT ON DECLARE @.dayofweekvarchar(20)SELECT @.dayofweek =CASE datepart(dw,getdate())WHEN 1THEN'Sunday'WHEN 2THEN'Monday'WHEN 3THEN'Tuesday'WHEN 4THEN'Wednesday'WHEN 5THEN'Thursday'WHEN 6THEN'Friday'WHEN 7THEN'Saturday'ENDDECLARE @.OutputPathvarchar(500)DECLARE @.DatabaseBackupFilevarchar(500)DECLARE @.FolderNamevarchar(25)DECLARE @.DatabaseNamevarchar(25)DECLARE @.strSQLvarchar(2000)DECLARE @.hostnamevarchar(255)SET @.hostname = (select replace(convert(varchar(255),serverproperty('SERVERNAME')),'\','_'))SET @.OutputPath ='C:\SQLBackups'DECLARE cur_BackupCURSOR FOR select name fromsysdatabaseswhere name !='tempdb'OPEN cur_Backup-- Fetch the db names from CursorFETCH NEXT FROM cur_BackupINTO @.DatabaseNameWHILE@.@.FETCH_STATUS = 0BEGINSET @.DatabaseBackupFile = @.OutputPath +'\' + @.hostname +'-' + @.DatabaseName +'-' + @.dayofweek +'.bak'print @.DatabaseBackupFileSET @.strSQL ='BACKUP DATABASE '+@.DatabaseName+' TO DISK = "'+ @.DatabaseBackupFile+'" WITH RETAINDAYS = 1, NOFORMAT, INIT, NAME = N''Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'PRINT @.strSQLEXEC (@.strSQL)FETCH NEXT FROM cur_BackupINTO @.DatabaseNameEND-- Distroy CursorCLOSE cur_BackupDEALLOCATE cur_BackupSET NOCOUNT OFF

Friday, March 23, 2012

Scheduled Task to restore Database ?

Scheduled Task to restore Database ?

Scheduled Task to restore Database ?

Wednesday, March 21, 2012

Scheduled Restore DB in EM?

Scheduled jobs not repeating (log shipping restore)

Wednesday, March 7, 2012

Schedule Local Package to perform FTP and DB restore

Tuesday, February 21, 2012

Schedule a scripted restore on 2005 Express from .bak file.

T-SQL Script

Blog Archive

About Me