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