MS SQL database restore scripts

When I worked with POS systems, especially when trouble-shooting customers problems, I need a scripts to restore database quickly in SQL Server Management Studio. But because each customer’s database may be different, for example it may have different file groups, backed up from different version of SQL Server, it is actually not that easy especially when you do it often. Here is the scripts I wrote to speed up the task, it only need several things: the backup file name, the new database name and the location to store the new database:

declare @db nvarchar(300)
declare @file nvarchar(300)
declare @file2 nvarchar(300)
declare @folder nvarchar(100)
declare @sql nvarchar(800)
declare @err int
SET NOCOUNT ON;
/*new database name*/
Set @db=’CARTERS’
set @db = upper(@db)

/* database backup file*/
Set @file= ‘C:\3000 Clients\Carters\12052501-CARTERS_Backup.BAK’
/* where the new database file will be located */
set @folder = ‘C:\Database\’

create table #res_temp(LogicalName varchar(100), PhysicalName varchar(200), [Type] char(1), FileGroupName varchar(50))
IF charindex(‘SQL Server 2008’,@@version)>0
BEGIN
  /* Sql server version 2008*/
  create table #res_temp1(
    LogicalName varchar(100), PhysicalName varchar(200), [Type] char(1), FileGroupName varchar(50),
    [Size] bigint, [MaxSize] bigint, FileId int, CreatedSN varchar(30), DropLSN varchar(30), UniqueId varchar(50),
    ReadInlyLSN bit, ReadWriteLSN bit, BackupSizeInBytes bigint, SourceBlockSize int, FileGroupId int,
    LogGroupGUID varchar(40), DifferentialBaseLSN varchar(30), DifferentialBaseGUID varchar(50),
    IsReadOnly bit, IsPresent bit, TDEThumbprint varchar(50))
  Insert Into #res_temp1(LogicalName, PhysicalName, [Type], FileGroupName,
    [Size], [MaxSize], FileId, CreatedSN, DropLSN, UniqueId,
    ReadInlyLSN, ReadWriteLSN, BackupSizeInBytes, SourceBlockSize, FileGroupId,
    LogGroupGUID, DifferentialBaseLSN, DifferentialBaseGUID, IsReadOnly, IsPresent,TDEThumbprint)
  Exec (‘RESTORE FILELISTONLY FROM DISK=”’+@File+””)
  Set @err = @@error
  If exists(Select 1 from #res_temp1) insert into #res_temp(LogicalName, PhysicalName, [Type], FileGroupName) select LogicalName, PhysicalName, [Type], FileGroupName from #res_temp1
  drop table #res_temp1
END
ELSE IF charindex(‘SQL Server 2005’,@@version)>0
BEGIN
  /* Sql server 2005*/
  create table #res_temp2(
    LogicalName varchar(100), PhysicalName varchar(200), [Type] char(1), FileGroupName varchar(50),
    [Size] bigint, [MaxSize] bigint, FileId int, CreatedSN varchar(30), DropLSN varchar(30), UniqueId varchar(50),
    ReadInlyLSN bit, ReadWriteLSN bit, BackupSizeInBytes bigint, SourceBlockSize int, FileGroupId int,
    LogGroupGUID varchar(40), DifferentialBaseLSN varchar(30), DifferentialBaseGUID varchar(50),
    IsReadOnly bit, IsPresent bit)
  Insert Into #res_temp2(LogicalName, PhysicalName, [Type], FileGroupName,
    [Size], [MaxSize], FileId, CreatedSN, DropLSN, UniqueId,
    ReadInlyLSN, ReadWriteLSN, BackupSizeInBytes, SourceBlockSize, FileGroupId,
    LogGroupGUID, DifferentialBaseLSN, DifferentialBaseGUID, IsReadOnly, IsPresent)
  Exec (‘RESTORE FILELISTONLY FROM DISK=”’+@File+””)
  Set @err = @@error
  If exists(Select 1 from #res_temp2) insert into #res_temp(LogicalName, PhysicalName, [Type], FileGroupName) select LogicalName, PhysicalName, [Type], FileGroupName from #res_temp2
  drop table #res_temp2
END
ELSE IF charindex(‘Microsoft SQL Server 2000’,@@version)>0
BEGIN
  /* Sql server 2000 */
  create table #res_temp3(
    LogicalName varchar(100), PhysicalName varchar(200), [Type] char(1), FileGroupName varchar(50),
    [Size] int, [MaxSize] bigint)
  Insert Into #res_temp3(LogicalName, PhysicalName, [Type], FileGroupName,
    [Size], [MaxSize])
  Exec (‘RESTORE FILELISTONLY FROM DISK=”’+@File+””)
  Set @err = @@error
  If exists(Select 1 from #res_temp3) insert into #res_temp(LogicalName, PhysicalName, [Type], FileGroupName) select LogicalName, PhysicalName, [Type], FileGroupName from #res_temp3
  drop table #res_temp3
END
ELSE
BEGIN
  Select @@VERSION
  Print ‘un-supported sql server version’
  set @err = 1
END

if @err = 0
BEGIN
  declare @LogicalName varchar(100), @PhysicalName varchar(200), @Type char(1), @FileGroupName varchar(50)
  declare @movefiles varchar(1000), @pos int, @lastpos int, @tsql varchar(200)
  declare @ret int
  set @movefiles=”
  DECLARE cur_info Cursor For
  select LogicalName, PhysicalName, Type, FileGroupName from #res_temp
  Open cur_info
  fetch next FROM cur_info into @LogicalName, @PhysicalName, @Type , @FileGroupName
  while @@fetch_status = 0
  BEGIN
    set @pos=0
    set @lastpos=0
    set @pos=charindex(‘.’,@PhysicalName)
    while(@pos)>0
    begin
      set @lastpos=@pos
      set @pos=charindex(‘.’,@PhysicalName,@pos+1)
    end
    /*new file name*/
    set @file2 = @folder+@db+substring(@PhysicalName,@lastpos,1000)
    set @movefiles=@movefiles+’,Move ”’+@LogicalName+”’ TO ”’+@file2+””
    /*delete exists file*/
    exec master..xp_fileexist @File2, @ret out
    If @ret = 1
    BEGIN
      SET @tsql = ‘Del “‘+@file2+'”‘
      Exec master..xp_cmdshell @tsql
    END
    fetch next FROM cur_info into @LogicalName, @PhysicalName, @Type , @FileGroupName
  END
  close cur_info
  deallocate cur_info
  /* remove last comma*/
  set @movefiles=’ With ‘ + substring(@movefiles,2,1000)
  /* drop existsing database */
  Set @Sql = ‘IF EXISTS(Select name from master..sysdatabases where name = ”’+@db+”’) Drop Database [‘+@db+’]’
  exec sp_executesql @sql
  /* generate restore scripts */
  Set @sql=’
  RESTORE DATABASE [‘+@db+’]
  FROM DISK = ”’+@file+””+@movefiles
  /* restore database*/
  exec sp_executesql @sql
  IF @@ERROR = 0
  BEGIN
    Print ‘Restore finished.’
  END
END
drop table #res_temp

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s