Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Oct 11 '12 at 19:46

This question came from our site for professional and enthusiast programmers.

    
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. –  Pondlife Oct 11 '12 at 13:58
add comment

2 Answers 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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.