SQL Server 2008 R2: Restore latest backup to new database from script

Question

We have a database for each major version of our system, for each customer, for both development and test. Like so:

WHATEVER44_CUSTOMERA_DEV WHATEVER44_CUSTOMERA_TEST WHATEVER44_CUSTOMERB_DEV WHATEVER44_CUSTOMERB_TEST WHATEVER44_CUSTOMERC_DEV WHATEVER44_CUSTOMERC_TEST

Once in a while, we restore the latest backup of each of these to new databases with a higher version number:

WHATEVER45_CUSTOMERA_DEV WHATEVER45_CUSTOMERA_TEST WHATEVER45_CUSTOMERB_DEV WHATEVER45_CUSTOMERB_TEST WHATEVER45_CUSTOMERC_DEV WHATEVER45_CUSTOMERC_TEST

We do this in Management Studio by right-clicking on the existing database, Task > Restore. It automatically selects the latest backup set and all we need to do is change the name of the database to restore to.

We want to automate this with a script that given a list of existing database names creates these new versions of the databases, without user interaction (apart from running the script). Is it possible?

Answer 1

This Is a Striped down version of the script I use to restore a great many databases at the same time. Hopefully I took out all of the parts that make it work for my scenario and left them with appropriate hints as to what to place in your version.

    -- Remove old DBs:
use [master]
go

DROP Database [YourDBName]
DROP Database [YourNextDBName]
DROP Database [AndSoOn]
DROP Database [AndSoForth]



GO

-- Create Restore List

Insert into [dbo].[BackupList] (DBName) Values ('YourDBName')
Insert into [dbo].[BackupList] (DBName) Values ('YourNextDBName')
Insert into [dbo].[BackupList] (DBName) Values ('AndSoOn')
Insert into [dbo].[BackupList] (DBName) Values ('AndSoForth')


GO

declare @counter [int]
declare @maxcounter [int]
declare @DBName [varchar](50)
declare @SQL1 [varchar](4000)
declare @SQL2 [varchar](4000)
declare @SQL3 [varchar](4000)
declare @SQL4 [varchar](4000)
set @counter = 0
select @maxcounter = max(id) from [dbo].[BackupList]


while @counter <= @maxcounter
BEGIN
                select @DBname = DBname from  .[dbo].[BackupList] where ID = @Counter

                IF @DBname LIKE '%WhateverYoursIsLike' 
                BEGIN
                SET @SQL1 = 'RESTORE DATABASE '+ QUOTENAME(@DBName) + ' FROM  DISK = ''YourBack-UpLocation' + @DBName + '.bak'' WITH  FILE = 1,  
                MOVE N''YourFileName'' TO ''YourRestoreLocation' + @DBName + '.mdf'', 
                MOVE N''YourFileName_log'' TO ''YourRestoreLocation' + @DBName + '.ldf'''
                exec(@sql1)
                print(@sql1)
                END



                set @counter = @counter + 1
END

GO
Use [em_support]
GO
truncate table [em_support].[dbo].[BackupList]
GO
DBCC CHECKIDENT (BackupList, RESEED, 0)
GO


--> Modify DB properties
DECLARE @DBName SYSNAME
DECLARE @Login SYSNAME
DECLARE @SQL NVARCHAR(4000)
DECLARE curModifyProperties CURSOR FOR
                SELECT [name]
                FROM sys.databases
                WHERE [name] LIKE '%WhateverYoursIsLike'

OPEN curModifyProperties
FETCH NEXT FROM curModifyProperties
INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN

                --> BEG - Set trustworthy property
                SET @SQL = 'ALTER DATABASE '+QUOTENAME(@DBName)+' SET TRUSTWORTHY ON'
                EXEC (@SQL)
                --> END - Set trustworthy property

                --> BEG - Set database owner
                SET @SQL = QUOTENAME(@DBName)+'.[dbo].[sp_changedbowner] ''sa'''
                EXEC (@SQL)
                --> END - Set database owner

FETCH NEXT FROM curModifyProperties
INTO @DBName
END
CLOSE curModifyProperties
DEALLOCATE curModifyProperties 
GO

I hope that helps or at least points you in the right direction.

Answer 2

You can use Sql Management Object to Restore and Backup

Backup:

ServerConnection serverConnection = new ServerConnection(".");
Server sqlServer = new Server(serverConnection);
String originalBackupPath = fileName;
BackupDeviceItem backupDevice = new BackupDeviceItem(originalBackupPath, DeviceType.File);

Backup backup = new Backup();
backup.Action = BackupActionType.Database;
backup.Database = "DB";
backup.Devices.Add(backupDevice);

backup.Initialize = true;
backup.Checksum = true;
backup.ContinueAfterError = true;
backup.Incremental = false;
backup.LogTruncation = BackupTruncateLogType.Truncate;

backup.SqlBackup(sqlServer);

Restore:

using (SqlConnection sqlcnn = new SqlConnection("Data Source=.;Integrated Security=True"))
{
   SqlCommand sqlcmd = new SqlCommand("ALTER DATABASE DB SET OFFLINE WITH ROLLBACK IMMEDIATE", sqlcnn);
  sqlcnn.Open();
  sqlcmd.ExecuteNonQuery();
  sqlcnn.Close();
}

ServerConnection serverConnection = new ServerConnection(".");
Server sqlServer = new Server(serverConnection);
String originalBackupPath = fileName;

BackupDeviceItem backupDevice = new BackupDeviceItem(originalBackupPath, DeviceType.File);
Restore restore = new Restore();
restore.Action = RestoreActionType.Database;
restore.Database = "DB";
restore.Devices.Add(backupDevice);
restore.Checksum = true;
restore.ContinueAfterError = true;
restore.SqlRestore(sqlServer);

The Alter command is used to take the db to Offline state if it was already in use.