2

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?

Improve this question
1
  • 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. Commented Oct 11, 2012 at 13:58

2 Answers 2

Reset to default
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.

Improve this answer
0

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.

Improve this answer