October 4, 2010 at 9:15 pm
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
October 5, 2010 at 3:19 am
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.
October 5, 2010 at 4:01 am
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
October 5, 2010 at 7:25 am
"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. 🙂
October 5, 2010 at 7:30 am
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
October 5, 2010 at 7:50 am
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
October 5, 2010 at 8:20 am
Why do this task with VB script when you can do the whole process with T-SQL scripts?
SQL DBA.
October 5, 2010 at 8:47 am
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
October 5, 2010 at 8:59 am
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.
October 5, 2010 at 10:10 am
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
October 5, 2010 at 12:23 pm
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.
October 5, 2010 at 12:33 pm
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 @UIDvarchar(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
October 5, 2010 at 12:39 pm
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 @UIDvarchar(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
October 5, 2010 at 5:50 pm
Very timely article, and discussion, as I am just about to setup an automated restore job for a DB. Thanks!
October 6, 2010 at 4:04 am
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 39 total)
You must be logged in to reply to this topic. Login to reply