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?

• Pondlife: The literal answer to your question is "yes". It would help if you can explain what you've already tried, what problems you have, what language you plan to script in etc. But the best thing you can do is just start trying to implement your scripts, and come back here if you have a specific problem that can be clearly defined.

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.