Automate Your Backup and Restore Tasks

  • 2Tall

    SSChampion

    Points: 14590

    Comments posted to this topic are about the item Automate Your Backup and Restore Tasks

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • ronclauw

    SSC Rookie

    Points: 27

    Hi,

    To drop a database (in this case: Develop) use this script:

    if exists (select name from master.sys.databases where name = 'Develop')

    begin

    alter database Develop

    set restricted_user with rollback immediate

    drop database Develop

    end

    Works all the time.

  • nikosag

    Valued Member

    Points: 50

    Hello,

    I do the same in my production system with the following, relatively simple 4 steps job:

    Step1: Alter dev database to Single user mode to prepare it for the restore. (No need to delete the database or the datafiles first.)

    ALTER DATABASE DevSystem SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    GO

    Step2 Restoring Production database from the production server using a network path, containind the backup file.

    (I use the MOVE command for the data and log files due to different storage paths. )

    RESTORE DATABASE [DevSystem] FROM DISK = N'\\ProductionServer\BackupFolder\Dev.bak' WITH FILE = 1,

    MOVE N'Datafile1' TO N'd:\MSSQL\Data\DevSystem_data1.mdf',

    MOVE N'Datafile2' TO N'd:\MSSQL\Data\DevSystem_data2.ndf',

    MOVE N'Logfile' TO N'd:\MSSQL\Data\DevSystem_log.LDF', NOUNLOAD, REPLACE, STATS = 90

    GO

    Step 3: Set the database to working state..

    ALTER DATABASE DevSystem SET MULTI_USER

    go

    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

  • thisisfutile

    Hall of Fame

    Points: 3486

    "I started as I do with most things by over complicating the solution! "

    LOL, I thought I was the only person that did that.

    I usually refer to it as making 3 left-turns to go right...but at least I end up in the right position. 🙂

  • JasonYousef

    SSCommitted

    Points: 1769

    That's a good one Nikos, Thanks for sharing your experience.

    nikosag (10/5/2010)


    Hello,

    I do the same in my production system with the following, relatively simple 4 steps job:

    Step1: Alter dev database to Single user mode to prepare it for the restore. (No need to delete the database or the datafiles first.)

    ALTER DATABASE DevSystem SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    GO

    Step2 Restoring Production database from the production server using a network path, containind the backup file.

    (I use the MOVE command for the data and log files due to different storage paths. )

    RESTORE DATABASE [DevSystem] FROM DISK = N'\\ProductionServer\BackupFolder\Dev.bak' WITH FILE = 1,

    MOVE N'Datafile1' TO N'd:\MSSQL\Data\DevSystem_data1.mdf',

    MOVE N'Datafile2' TO N'd:\MSSQL\Data\DevSystem_data2.ndf',

    MOVE N'Logfile' TO N'd:\MSSQL\Data\DevSystem_log.LDF', NOUNLOAD, REPLACE, STATS = 90

    GO

    Step 3: Set the database to working state..

    ALTER DATABASE DevSystem SET MULTI_USER

    go

    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

    SSC Rookie

    Points: 44

    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

  • SanjayAttray

    SSChampion

    Points: 13157

    Why do this task with VB script when you can do the whole process with T-SQL scripts?

    SQL DBA.

  • 2Tall

    SSChampion

    Points: 14590

    Hi.

    Why use VB scripts.

    I said not everyone would agree with my approach! 🙂

    The VB scripts supported what I wanted to achieve.

    Kind Regards,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715979

    SanjayAttray (10/5/2010)


    Why do this task with VB script when you can do the whole process with T-SQL scripts?

    Do you somehow think that T-SQL is "better"

    I think either approach can work well. I like VB for file work as it appears cleaner to me.

  • SQLRNNR

    SSC Guru

    Points: 281210

    Nice article and an important topic. Thanks for sharing.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Joe Blow-413159

    Valued Member

    Points: 62

    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

    Valued Member

    Points: 62

    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

    Valued Member

    Points: 62

    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

    SSChampion

    Points: 13482

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

  • nikosag

    Valued Member

    Points: 50

    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:

    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

Viewing 15 posts - 1 through 15 (of 40 total)

You must be logged in to reply to this topic. Login to reply