October 6, 2010 at 12:29 pm
Thanks!!! I appreciate the info.
October 7, 2010 at 9:31 am
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 😉
October 7, 2010 at 10:10 am
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
October 7, 2010 at 11:32 am
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.
October 20, 2010 at 5:39 am
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.
September 20, 2013 at 12:24 am
Philip,
Although I meticulously always do
Set myObject = Nothing
at the end of vb scripts, I have never seen this error message you mentioned (on those rare occassions when I forgot).
So how exactly do you run the script?
September 20, 2013 at 1:57 am
Good article; thanks for sharing your experience Phil ...
Below is my solution ...
In my scenario, I needed to restore a Live DB to any of our 4 QA environments. As these are QA databases, I suppose the developpers left the DB, so no need to kill the connections.
The different steps are:
- does the DB exist?
- are there still active connections on the DB?
- set the variables (file names, ...) of the different QA DBs
- restore
- correction of the orphan user to log in
I assume that the DB to restore was copied onto the QA SQL server.
The script is not automated and is supposed to be run in SSMS on an ad-hoc basis.
Glad to share my solution with you ...
-- --------------------------------------------------------------------------------------
--
-- Script to restore a database
--
-- --------------------------------------------------------------------------------------
-- script runs on master!
use master
-- initialisations
declare@sqlnvarchar(max),
@nbconnectionssmallint,
@dbnamenvarchar(500),
@filename_of_backupnvarchar(500),
@filenvarchar(500),
@mdfmovenvarchar(500),
@mdftonvarchar(500),
@ldfmovenvarchar(500),
@ldftonvarchar(500)
-- --------------------------------------------------------------------------------------
-- !!!!! DB to restore !!!!!
-- --------------------------------------------------------------------------------------
select @dbname = 'TST_QA1'
--select @dbname = 'TST_QA2'
select @filename_of_backup = N'D:\SQLBU\LIVE_20130827_01-00-00_FULL.BAK'
-- --------------------------------------------------------------------------------------
-- Does the DB exist on the SQL server?
-- --------------------------------------------------------------------------------------
if db_id(@dbname) is null
begin
print 'La base de données ' + @dbname + ' n''existe pas sur ce serveur - Restore impossible'
return
end
-- --------------------------------------------------------------------------------------
-- How many active connections on the DB?
-- --------------------------------------------------------------------------------------
SELECT @nbconnections = COUNT(dbid)
FROMsys.sysprocesses
WHEREDB_NAME(dbid) = @dbname
GROUP BY dbid, loginame
-- if > 0 --> restore is not possible
if @nbconnections > 0
begin
print 'There are still active connections on ' + @dbname + ' - Restore impossible'
return
end
-- --------------------------------------------------------------------------------------
-- Set variables according to the QA DB to restore to
-- --------------------------------------------------------------------------------------
if @dbname = 'TST_QA1'
begin
select
@file = N'hiName',
@mdfmove = N'hiName',
@mdfto = N'D:\SQLDATA\TST_QA1.mdf',
@ldfmove = N'hiName_log',
@ldfto = N'D:\SQLLOG\TST_QA1_0.ldf'
end
else
if @dbname = 'TST_QA2'
begin
select
@file = N'hiName',
@mdfmove = N'hiName',
@mdfto = N'D:\SQLDATA\TST_QA2.mdf',
@ldfmove = N'hiName_log',
@ldfto = N'D:\SQLLOG\TST_QA2.ldf'
end
else
if @dbname = 'TST_QA3'
begin
select
@file = N'hiName',
@mdfmove = N'hiName',
@mdfto = N'D:\SQLDATA\TST_QA3DATA.mdf',
@ldfmove = N'hiName_log',
@ldfto = N'D:\SQLLOG\TST_QA3_log.ldf'
end
else
if @dbname = 'TST_QA4'
begin
select
@file = N'hiName',
@mdfmove = N'hiName',
@mdfto = N'D:\SQLDATA\TST_QA4.mdf',
@ldfmove = N'hiName_log',
@ldfto = N'D:\SQLLOG\TST_QA4_log.ldf'
end
else
begin
print 'Database ' + @dbname + ' not defined in the script - Restore impossible'
return
end
-- --------------------------------------------------------------------------------------
-- Restoring
-- --------------------------------------------------------------------------------------
select @sql = '
RESTORE DATABASE [' + @dbname + ']' +
' FILE = N''' + @file +
''' FROM DISK = N''' + @filename_of_backup +
''' WITH FILE = 1,' +
' MOVE N''' + @mdfmove + ''' TO N''' + @mdfto + ''',' +
' MOVE N''' + @ldfmove + ''' TO N''' + @ldfto + ''',' +
' NOUNLOAD, REPLACE, STATS = 10'
exec sp_executesql @sql
--select @sql = '
--RESTORE DATABASE [' + @dbname + ']' +
--' FROM DISK = N''' + @filename_of_backup +
--''' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5'
--print @sql
--exec sp_executesql @sql
--select @sql = '
--RESTORE DATABASE [' + @dbname + ']' +
--' FROM DISK = N''' + @filename_of_backup +
--''' WITH FILE = 3, NOUNLOAD, STATS = 5'
--print @sql
--exec sp_executesql @sql
-- --------------------------------------------------------------------------------------
-- Correction of the orphan user to log in
-- --------------------------------------------------------------------------------------
-- the script only runs on the active DB --> so activation of the QA DB required
-- !!! SQLCMD mode must be on for this to run !!!
if @dbname = 'TST_QA1'
begin
:setvar dbname1 "TST_QA1"
use $(dbname1)
end
else
if @dbname = 'TST_QA2'
begin
:setvar dbname2 "TST_QA2"
use $(dbname2)
end
else
if @dbname = 'TST_QA3'
begin
:setvar dbname3 "TST_QA3"
use $(dbname3)
end
else
if @dbname = 'TST_QA4'
begin
:setvar dbname4 "TST_QA4"
use $(dbname4)
end
else
begin
print 'Database ' + @dbname + ' not defined for the mapping of TST_login - Mapping impossible'
return
end
-- SP run for correction
Exec sp_change_users_login @action='Update_One', @UserNamePattern= [TST_login] , @LoginName=[TST_login]
EDIT: changed code tag to sql
September 20, 2013 at 2:47 am
I have a philosophical question: why would anyone want to daily automate the restore to dev?
What happens if a developer didn't put the developments on production?
to automate for QA I understand but for dev...
September 20, 2013 at 4:05 am
VB vs T-SQL 😀
September 20, 2013 at 4:16 am
Hi to all. It's been a while since I did this! 🙂
I now use T-SQL scripts in 3 steps. The reason for restoring daily is our planning team like to have an up to date copy of the live data so they can run forecasting simulations.
My SQL Job has 3 steps:
Step1: Backup the Production System
BACKUP DATABASE 'MyDB'
TO DISK = 'F:\Test Restore\MyDB.bak'
WITH INIT
Step 2: Kill any connections on the target DB
-- Create the sql to kill the active database connections
DECLARE @execSql varchar(1000)
DECLARE @databaseName varchar(100)
-- Set the database name for which to kill the connections
SET @databaseName = 'MyDB'
SET @execSql =
''SELECT @execSql = @execSql + 'kill ' + CONVERT(CHAR(10), spid) + ' '
from master.dbo.sysprocesses
WHERE db_name(dbid) = @databaseName AND status <> 'background' AND status IN ('runnable','sleeping') AND DBID <> 0 AND spid <> @@spid
EXEC (@execSql)
Step 3: Restore
RESTORE DATABASE MyDB
FROM DISK = 'F:\Test Restore\MyDB.bak'
WITH REPLACE
[/Code]
Thanks,
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
September 20, 2013 at 7:53 am
Hi,
The only reason why i think tsql is better is if you work with multiple DBA's and if the job fails for whatever reason it is much easier to troubleshoot the issue in TSQL.
Just my 2 cents:-D
September 20, 2013 at 12:37 pm
Steve Jones - SSC Editor (10/5/2010)
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"
Yes... but it's mostly a personal preference. To play on other's words, "Just because something CAN be done in T-SQL, doesn't mean that it SHOULDN'T." 😉
I think either approach can work well. I like VB for file work as it appears cleaner to me.
While I agree that it's nice tight code, I have to ask... Cleaner than what?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 20, 2013 at 2:23 pm
There is also undocumented procedure xp_delete_file that can be used to delete files in a particular folder. Use at your own risk;-)
September 22, 2013 at 2:05 pm
Seems if you are gonna use script, why not powershell?
September 23, 2013 at 8:19 am
2Tall (9/20/2013)
Hi to all. It's been a while since I did this! 🙂I now use T-SQL scripts in 3 steps. The reason for restoring daily is our planning team like to have an up to date copy of the live data so they can run forecasting simulations.
My SQL Job has 3 steps:
Step1: Backup the Production System
BACKUP DATABASE 'MyDB'
TO DISK = 'F:\Test Restore\MyDB.bak'
WITH INIT
Step 2: Kill any connections on the target DB
-- Create the sql to kill the active database connections
DECLARE @execSql varchar(1000)
DECLARE @databaseName varchar(100)
-- Set the database name for which to kill the connections
SET @databaseName = 'MyDB'
SET @execSql =
''SELECT @execSql = @execSql + 'kill ' + CONVERT(CHAR(10), spid) + ' '
from master.dbo.sysprocesses
WHERE db_name(dbid) = @databaseName AND status <> 'background' AND status IN ('runnable','sleeping') AND DBID <> 0 AND spid <> @@spid
EXEC (@execSql)
Step 3: Restore
RESTORE DATABASE MyDB
FROM DISK = 'F:\Test Restore\MyDB.bak'
WITH REPLACE
[/Code]
Thanks,
Phil.
Be careful with killing SPIDs. I forget the URL for the CONNECT item on this but you could easily end up with a CPU consuming 0% ROLLBACK that cannot be cancelled without bouncing the service. And, yeah... I'm speaking from actual experience with the problem. It does happen.
It's far better to set the database to SINGLE user mode with ROLLBACK IMMEDIATE, immediately return it to MULTI user mode (in case you lose the connection, you don't want any gereedy web services to snap up the only connection), and then do the restore. The web services don't react quickly enough to get in between.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply