SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automate Your Backup and Restore Tasks


Automate Your Backup and Restore Tasks

Author
Message
Joe Blow-413159
Joe Blow-413159
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 58
We do basically the same thing in my environment.
1) Copy file to DEV server - I don't trust network connections during the restore, no one seems to handle them well
2) Put existing DB into SINGLE_USER mode (error is thrown if the DB doesn't exist yet because I haven't added the 'if' statement)
3) Restore all _Data and _Log files contained within the backup - This allows the same script/SP to be used on many DBs with only a few variables getting changed.

I kept the file copy and everything else contained within the T-SQL code to make it more portable and easier to replicate at new sites. Also, I fair amount of output via PRINT which I log to a file via the SQL Job Agent, that is just a personal preference.
Joe Blow-413159
Joe Blow-413159
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 58
Here is my code for SQL 2008 (the RESTORE_FILELIST_ONLY gives different output based on SQL version).

PRINT 'Starting up...'
PRINT CONVERT(varchar(100),GETDATE(),121)
DECLARE @dest_dbname varchar(255)
DECLARE @source_path varchar(255)
DECLARE @dest_filepath varchar(255)
DECLARE @log_filepath varchar(255)
DECLARE @stage_db_data_dir varchar(255)
DECLARE @stage_db_log_dir varchar(255)
-- These should be the only variables that you change
SET @dest_dbname = '[RTS_STAGE]'
SET @source_path = '\\srv-sql2008\SQLBackups\SomeDB\'
SET @dest_filepath = '\\srv-devsql2k8\MostRecent\SomeDB.bak'
SET @log_filepath = '\\srv-devsql2k8\MostRecent\SomeDB_copy_log.txt'
SET @stage_db_data_dir = 'E:\MSSQLData\'
SET @stage_db_log_dir = 'G:\MSSQLTranLogs\'



DECLARE @source_mask varchar(255)
DECLARE @dir_cmd varchar(255)
DECLARE @copy_cmd varchar(255)

DECLARE @singleuser_sqlcmd nvarchar(255)
DECLARE @multiuser_sqlcmd nvarchar(255)
DECLARE @dbrestore_sqlcmd nvarchar(2000)

SET NOCOUNT ON

/* ********
This block pulls a DIR from the path specified above and
then generates and executes a COPY command based on the
most recent file. NOTE: does nothing if no matching file is found
***** */
PRINT 'searching for a file...'
PRINT CONVERT(varchar(100),GETDATE(),121)
SET @source_mask = @source_path +'*.bak'
SET @dir_cmd = 'dir "'+@source_mask+'" /o-d /b'
CREATE TABLE #T ([output] varchar(255))

--fill temp table with the dir listing
INSERT INTO #T
EXECUTE xp_cmdshell @dir_cmd

DELETE FROM #T WHERE [output] IS NULL --remove empty lines

IF EXISTS (SELECT * FROM #T WHERE [output] LIKE '%.bak')
BEGIN
PRINT 'file found, copying file...'
PRINT CONVERT(varchar(100),GETDATE(),121)
--build COPY command
SELECT TOP 1 @copy_cmd = 'copy /V /B /Y /Z "' + @source_path + [output] +'" "'+@dest_filepath +'" > "'+@log_filepath+'"'
FROM #T
ORDER BY [output] DESC
EXEC xp_cmdshell @copy_cmd --run COPY command

SET @singleuser_sqlcmd = 'ALTER DATABASE '+@dest_dbname+' SET single_user WITH ROLLBACK IMMEDIATE'
SET @dbrestore_sqlcmd = 'RESTORE DATABASE '+@dest_dbname+' FROM DISK = '''+@dest_filepath+''' WITH REPLACE '
SET @multiuser_sqlcmd = 'ALTER DATABASE '+@dest_dbname+' SET multi_user WITH ROLLBACK IMMEDIATE'

CREATE TABLE #FILELISTONLY (
[LogicalName] [varchar](255) NULL, [PhysicalName] [varchar](255) NULL, [Type] [varchar](50) NULL, [FileGroupName] [varchar](255) NULL, [Size] [varchar](50) NULL, [MaxSize] [varchar](50) NULL,
[FileId] [varchar](50) NULL, [CreateLSN] [varchar](50) NULL, [DropLSN] [varchar](50) NULL, [UniqueId] [varchar](50) NULL, [ReadOnlyLSN] [varchar](50) NULL, [ReadWriteLSN] [varchar](50) NULL, [BackupSizeInBytes] [varchar](50) NULL,
[SourceBlockSize] [varchar](50) NULL, [FileGroupId] [varchar](50) NULL, [LogGroupGUID] [varchar](50) NULL, [DifferentialBaseLSN] [varchar](50) NULL,
[DifferentialBaseGUID] [varchar](50) NULL, [IsReadOnly] [varchar](50) NULL, [IsPresent] [varchar](50) NULL, [TDEThumbprint] [varchar](50) NULL
)
insert into #FILELISTONLY
exec('Restore FILELISTONLY FROM DISK='''+@dest_filepath+'''')

WHILE EXISTS (SELECT * from #FILELISTONLY WHERE [Type] IN ('D','L'))
BEGIN
DECLARE @UID varchar(50)
SELECT TOP 1 @UID=UniqueID FROM #FILELISTONLY WHERE [Type] IN ('D','L') ORDER BY FileID,UniqueId
SELECT TOP 1 @dbrestore_sqlcmd=@dbrestore_sqlcmd+
CASE [Type]
WHEN 'D' THEN ' , MOVE '''+[LogicalName]+''' TO '''+@stage_db_data_dir+[LogicalName]+'.mdf'' '
WHEN 'L' THEN ' , MOVE '''+[LogicalName]+''' TO '''+@stage_db_log_dir+[LogicalName]+'.ldf'' '
END
FROM #FILELISTONLY WHERE UniqueId=@UID

DELETE FROM #FILELISTONLY WHERE UniqueId=@UID
END
drop table #FILELISTONLY

/* ********
This block puts the DB in single user mode (killing all open connections) then
does a restore and finally puts the DB back in multi-user mode.
***** */
PRINT 'putting DB in single-user mode...'
PRINT CONVERT(varchar(100),GETDATE(),121)
EXECUTE sp_executesql @statement = @singleuser_sqlcmd

PRINT 'restoring DB='+@dest_dbname+' from FILE='+@dest_filepath+'... ('+@dbrestore_sqlcmd+')'
PRINT CONVERT(varchar(100),GETDATE(),121)
EXECUTE sp_executesql @statement = @dbrestore_sqlcmd

PRINT 'putting DB back in multi-user mode...'
PRINT CONVERT(varchar(100),GETDATE(),121)
EXECUTE sp_executesql @statement = @multiuser_sqlcmd

PRINT 'finished.'
PRINT CONVERT(varchar(100),GETDATE(),121)
END

DROP TABLE #T
Joe Blow-413159
Joe Blow-413159
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 58
Here is the SQL2000 code (not that the SQL2000 Job Agent has a lower limit on the amount of chars allowed in the T-SQL statement so you have to put this code into a Stored Proc.

PRINT 'Starting up...'
PRINT CONVERT(varchar(100),GETDATE(),121)
DECLARE @dest_dbname varchar(255)
DECLARE @source_path varchar(255)
DECLARE @dest_filepath varchar(255)
DECLARE @log_filepath varchar(255)
DECLARE @stage_db_data_dir varchar(255)
DECLARE @stage_db_log_dir varchar(255)
-- These should be the only variables that you change
SET @dest_dbname = '[Intranet_Stage]'
SET @source_path = '\\srv-sql2000\SQLBackups\Intranet\'
SET @dest_filepath = '\\srv-devsql2k\MostRecent\Intranet.bak'
SET @log_filepath = '\\srv-devsql2k\MostRecent\Intranet_copy_log.txt'
SET @stage_db_data_dir = 'E:\SQL Server\Data\MSSQL\Data\'
SET @stage_db_log_dir = 'G:\SQL Server\Data\MSSQL\TLogs\'

DECLARE @source_mask varchar(255)
DECLARE @dir_cmd varchar(255)
DECLARE @copy_cmd varchar(255)

DECLARE @singleuser_sqlcmd nvarchar(255)
DECLARE @multiuser_sqlcmd nvarchar(255)
DECLARE @dbrestore_sqlcmd nvarchar(2000)

SET NOCOUNT ON

/* ********
This block pulls a DIR from the path specified above and
then generates and executes a COPY command based on the
most recent file. NOTE: does nothing if no matching file is found
***** */
PRINT 'searching for a file...'
PRINT CONVERT(varchar(100),GETDATE(),121)
SET @source_mask = @source_path +'*.bak'
SET @dir_cmd = 'dir "'+@source_mask+'" /o-d /b'
CREATE TABLE #T ([output] varchar(255))

--fill temp table with the dir listing
INSERT INTO #T
EXECUTE [master].[dbo].xp_cmdshell @dir_cmd

DELETE FROM #T WHERE [output] IS NULL --remove empty lines

IF EXISTS (SELECT * FROM #T WHERE [output] LIKE '%.bak')
BEGIN
PRINT 'file found, copying file...'
PRINT CONVERT(varchar(100),GETDATE(),121)
--build COPY command
SELECT TOP 1 @copy_cmd = 'copy /V /B /Y /Z "' + @source_path + [output] +'" "'+@dest_filepath +'" > "'+@log_filepath+'"'
FROM #T
ORDER BY [output] DESC
EXEC [master].[dbo].xp_cmdshell @copy_cmd --run COPY command

SET @singleuser_sqlcmd = 'ALTER DATABASE '+@dest_dbname+' SET single_user WITH ROLLBACK IMMEDIATE'
SET @dbrestore_sqlcmd = 'RESTORE DATABASE '+@dest_dbname+' FROM DISK = '''+@dest_filepath+''' WITH REPLACE '
SET @multiuser_sqlcmd = 'ALTER DATABASE '+@dest_dbname+' SET multi_user WITH ROLLBACK IMMEDIATE'

CREATE TABLE #FILELISTONLY (
[LogicalName] [varchar](255) NULL, [PhysicalName] [varchar](255) NULL, [Type] [varchar](50) NULL, [FileGroupName] [varchar](255) NULL, [Size] [varchar](50) NULL, [MaxSize] [varchar](50) NULL
)
declare @st nvarchar(255)
set @st='Restore FILELISTONLY FROM DISK='''+@dest_filepath+''''

insert into #FILELISTONLY
exec [master].[dbo].sp_executesql @statement=@st

--exec('Restore FILELISTONLY FROM DISK='''+@dest_filepath+'''')

WHILE EXISTS (SELECT * from #FILELISTONLY WHERE [Type] IN ('D','L'))
BEGIN
DECLARE @UID varchar(250)
SELECT TOP 1 @UID=[PhysicalName] FROM #FILELISTONLY WHERE [Type] IN ('D','L') ORDER BY Type
SELECT TOP 1 @dbrestore_sqlcmd=@dbrestore_sqlcmd+
CASE [Type]
WHEN 'D' THEN ' , MOVE '''+[LogicalName]+''' TO '''+@stage_db_data_dir+[LogicalName]+'.mdf'' '
WHEN 'L' THEN ' , MOVE '''+[LogicalName]+''' TO '''+@stage_db_log_dir+[LogicalName]+'.ldf'' '
END
FROM #FILELISTONLY WHERE [PhysicalName]=@UID

DELETE FROM #FILELISTONLY WHERE [PhysicalName]=@UID
END
drop table #FILELISTONLY

/* ********
This block puts the DB in single user mode (killing all open connections) then
does a restore and finally puts the DB back in multi-user mode.
***** */
PRINT 'putting DB in single-user mode...'
PRINT CONVERT(varchar(100),GETDATE(),121)
EXECUTE [master].[dbo].sp_executesql @statement = @singleuser_sqlcmd

PRINT 'restoring DB='+@dest_dbname+' from FILE='+@dest_filepath+'... ('+@dbrestore_sqlcmd+')'
PRINT CONVERT(varchar(100),GETDATE(),121)
EXECUTE [master].[dbo].sp_executesql @statement = @dbrestore_sqlcmd

PRINT 'putting DB back in multi-user mode...'
PRINT CONVERT(varchar(100),GETDATE(),121)
EXECUTE [master].[dbo].sp_executesql @statement = @multiuser_sqlcmd
PRINT 'finished.'
PRINT CONVERT(varchar(100),GETDATE(),121)
END

DROP TABLE #T
GO
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2664 Visits: 2204
Very timely article, and discussion, as I am just about to setup an automated restore job for a DB. Thanks!
nikosag
nikosag
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 23
This stored procedure maps an existing database user to a SQL Server login.
It's true that is a bit comfusing, because the SQL login name (found under SQLserverX->sercurity->Logins and and the database user name (found under databaseX->security->users, for SQL Server are different things.

If for example you have an SQL Server "sqlA" and a user with login name "nikos", whitch is the owner of some tables in "DbX", when backup and restore this database to a different SQL server, even if the "nikos" login name exists, it's not the same login (has different UID) with the previous and the "nikos" database name is orphaned.
If you try to login as nikos and cast a "Select * from nikos.TableX" statement you ger an error.

The procedure has the following syntax:
p_change_users_login Update_One @DBUserName @SQLLoginName

for more information you can go to:
[url=http://msdn.microsoft.com/en-us/library/ms174378.aspx][/url]



lloyd.tapper (10/5/2010)

Nikos, I do pretty much the same thing that you do. However, could you explain Step #4 a bit more. The one thing I generally have trouble with is the logins and haven't gotten a satisfactory solution yet. What does this step do?


Step 4: Finally, if the dev database is on a different SQL Servel like in my case is essential to map the login username of the 1st SQL to login username of the 2nd with the following command:

sp_change_users_login update_one ,exampleusername,exampleusername
go

Regards

Nikos

lloyd.tapper
lloyd.tapper
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 15
Thanks!!! I appreciate the info.
Misha_SQL
Misha_SQL
SSC Eights!
SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)

Group: General Forum Members
Points: 958 Visits: 1004
I especially like the fact that the thinking process of trying, correcting and trying again has been very well presented. And I thought that everybody else got it right the first time ;-)



Randy Doub
Randy Doub
Old Hand
Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)

Group: General Forum Members
Points: 384 Visits: 778
Thanks for the info. I've been doing this for some time and never thought to kill connections as I'm doing it at 1am. Guess I've been lucky. I'll have to add that to my process.

For any RedGate users out there:
I use RedGate's SQL Backup for backup, and restore to a server without a SQL Backup License. So my steps are to:
-Back up the database
-Convert the .sqb to an MTF file with a static file name
-Restore using Move and Replace

I use Mon_dd_yyyy_Dbname_FULL.sqb for backup file names. So I know what the current backup file name should be every night. Here's the procedure I use to convert the .sqb to MTF:

create procedure convert_sqb_sp
as
/*
Convert the .sqb backup file created from Red-Gate's SQL Backup
to a MTF .bak file to restore on the MyRptDB database.
*/
set nocount on
declare @cmd varchar(500)
declare @f1 varchar(200), @f2 varchar(200), @rtn int
declare @exitcode int, @sqlerrorcode int
select @f1 = 'c:\sql_backup\' + replace(replace(convert(varchar(12),getdate(),107),' ','_'),',','') + '_MyLiveDB_FULL.SQB'
select @f2 = 'c:\sql_backup\MyRptDB_Restore.bak'

select @cmd = 'del ' + @f2
exec @rtn = master..xp_cmdshell @cmd
if @rtn <> 0
begin
raiserror('MyRptDB Convert - Delete .bak failed', 16,1)
return 1
end
else
begin
select @cmd = '-SQL "CONVERT ''' + @f1 + ''' TO ''' + @f2 + '''"'
exec master..sqlbackup @cmd, @exitcode OUT, @sqlerrorcode OUT
if (@exitcode >= 500) or (@sqlerrorcode <> 0)
begin
raiserror('MyRptDB Convert - Convert .sqb failed', 16,1)
return 1
end
end

Alex Diaz-329446
Alex Diaz-329446
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 165
Hi,

This is a good topic. Thank you for sharing.
In my case I needed to provide the developers the ability to restore a copy of production in the development environment. There are several databases so the developers need to specify the database to restore. The other consideration is that the permissions in Production are more restricted than the permissions in Development and when the database is restored from one SQL instance to another the loggins got messed up and need to be synchronized.
In order to accomplish the task I used a SSIS package to take advantage of parameters and a SQL Job that runs the package and can be executed by the developer using the command line. The steps in the SSIS package are:

1) Get backup info: File name, backup location and restore location
2) Copy backup file to restore location
3) Script Task to fill tsql variables
4) Generate the grant db access script using system views and store it in a variable
5) Generate the grant db roles script using system views and store it in a variable
6) Set database in single user mode with rollback immediate
7) Restore the database using the tsql stored in one of the variables
8) Remove the security coming from production
9) Restore the security using the tsql generated in steps 4 and 5

These are just the steps in general. If anybody is interested in the details of a specific step just let me know.
Daniel Iturriaga
Daniel Iturriaga
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 54
Particularmente, lo automatizo mediante dos jobs, uno de backup en un punto compartido en la red, y otro con el restore correspondiente... y listo.
Para pisar la base de desarrollo sin que haya usuarios conectados... es más que simple:
ALTER DATABASE [DataBase]
SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE [DataBase]
SET ONLINE

El ejemplo planteado está muy bien pero me resultado demasiado tedioso.

Enjoy!

Saludos, Daniel.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search