Automate Your Backup and Restore Tasks

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Philip Horan » Automate Your Backup and Restore Tasks

20 posts, Page 2 of 2

««1 2

Automate Your Backup and Restore Tasks

Rate Topic

Display Mode

Topic Options

Author

Message

Joe Blow-413159

Posted Tuesday, October 05, 2010 12:23 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 18, 2011 6:03 PM
Points: 4, 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.

Post #998655

Joe Blow-413159

Posted Tuesday, October 05, 2010 12:33 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 18, 2011 6:03 PM
Points: 4, 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

Post #998667

Joe Blow-413159

Posted Tuesday, October 05, 2010 12:39 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 18, 2011 6:03 PM
Points: 4, 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

Post #998672

UMG Developer

Posted Tuesday, October 05, 2010 5:50 PM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 11:21 AM
Points: 2,163, Visits: 2,148

Very timely article, and discussion, as I am just about to setup an automated restore job for a DB. Thanks!

Post #998876

nikosag

Posted Wednesday, October 06, 2010 4:04 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, January 02, 2012 4:50 AM
Points: 10, 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

Post #999098

lloyd.tapper

Posted Wednesday, October 06, 2010 12:29 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 13, 2012 2:19 PM
Points: 2, Visits: 12

Thanks!!! I appreciate the info.

Post #999769

mishaluba

Posted Thursday, October 07, 2010 9:31 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:54 PM
Points: 525, Visits: 617

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 Wink

Post #1000632

Randy Doub

Posted Thursday, October 07, 2010 10:10 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 4:28 PM
Points: 131, Visits: 597

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

Post #1000668

Alex Diaz-329446

Posted Thursday, October 07, 2010 11:32 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, May 04, 2013 1:10 PM
Points: 4, Visits: 97

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.

Post #1000745

Daniel Iturriaga

Posted Wednesday, October 20, 2010 5:39 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, April 24, 2012 9:04 AM
Points: 8, 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.

Post #1007625

« Prev Topic | Next Topic »

20 posts, Page 2 of 2

««1 2

Permissions