How can I append a date to a restore?

Transact-SQL
1

I do need to restore a database, in a weekly or daily basis from server A to server B. But! if the database is already there, I need to rename it, add existing date, and restore the other one there too.

I thought about this:

IF EXISTS
(
SELECT name
FROM SYS.databases
WHERE name='MyDatabase'
)
BEGIN
ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE MyDatabase Modify Name = MyDatabase_TodaysDate ;
ALTER DATABASE MyDatabase_TodaysDate SET MULTI_USER;
END

where "TodaysDate" should be a variable, and of course, appended to the ALTER command.

So at any point in time, if the database was already there, I should have:

MyDatabase
MyDatabase_09_20_2016

Where 09_20_2016 on this case is the oldest version

How can I accomplish this? I imagine I will need dynamic sql.

2

Yes, you will need dynamic SQL, something like this:

USE MyDatabase
DECLARE @sql varchar(8000)

IF EXISTS
(
SELECT name 
FROM SYS.databases
WHERE name='MyDatabase'
)
BEGIN
    ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 
    SET @sql = 'ALTER DATABASE MyDatabase Modify Name = MyDatabase_' + 
        CONVERT(varchar(8), GETDATE(), 112)
    /*PRINT @sql*/
    EXEC(@sql)
    ALTER DATABASE MyDatabase_TodaysDate SET MULTI_USER;
END /*IF*/
1 Like
3

Thanks Scott. I will take a look later tonight and check.

1 Like
4

Just a thought:

Would you, instead, restore the backup file to a TempNameDB and then, once the restore has completed, see if there is an existing database matching the name and THEN do a Rename to MyDatabase_TodaysDate and, either way, then rename TempNameDB to MyDatabase

I'm just thinking that Rename first might leave you with no MyDatabase at the end of the process (e.g. disk full on restore - because the purged of all the old MyDatabase_yyyymmdd databases failed!!). if it is critical that MyDatabase always exists, even if "stale", then I would favour the fail-safe route.

5

It does not work. Here is the code I have:

DECLARE @sql varchar(8000)

IF EXISTS
(
SELECT name
FROM SYS.databases
WHERE name='MyTest'
)
BEGIN
ALTER DATABASE MyTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
SET @sql =
'ALTER DATABASE MyTest Modify Name = MyTest' + CONVERT(varchar(8), GETDATE(), 112) + ';'+'ALTER DATABASE MyTest'+ CONVERT(varchar(8), GETDATE(), 112) + ' SET MULTI_USER;'+
'Test'
PRINT @sql
--EXEC(@sql)
END

RESTORE DATABASE [MyTest]
FROM DISK = N'\path\MyTest.bak'
WITH FILE = 1, MOVE N'MyTest' TO N'm:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\MyTest.mdf',
MOVE N'MyTest_log' TO N'l:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\MyTest_log.ldf', NOUNLOAD, REPLACE, STATS = 5

And it its not working, because on the 2nd attempt, after I renamed the existing database, the restore fails because the data files have the same names.

How can I dynamically rename the mdf and ldf names, so they will be different every time I rename the database name? Let's say appending the date, as we are already doing on the actual database name?

6

Instead of trying to rename the files - you should restore the new database with date stamped file names. Kristen has the better idea - that is, restore with a date stamp - then go through a rename process.

Ex:

Restore database as MyTest_{date}
Check for MyTest database - if exists, rename to MyTest_{otherdate}
Rename MyTest_{date} to MyTest

To get the appropriate date values - you could read the file name from the existing database and parse out the date stamp using that for {otherdate}.

Your restore command would look like this:

RESTORE DATABASE MyTest
FROM DISK = ...
WITH FILE = 1,
MOVE N'MyTest' TO N'M:\xxx\MyTest_{date}.mdf',
MOVE N'MyTestLog' TO N'M:\xxx\MyTest_log_{date}.ldf',
REPLACE,
STATS=5;

Remove the REPLACE if you want to insure the process fails if the dated version already exists.

7

I can't! That's not what I was asked. That's how I would do it, but not what I've been asked to.

Development team does not want a time stamp on the most recent restored database, only on the old copy, after being renamed. The main goal of all this is keeping oldest database version just in case they have not tested all their code, and most recent databases with the original name.

-- EDIT --

I think I need coffee :slight_smile: .... I can, actually, and spoke to the Developer. I can restore with a time stamp and rename, so this will effectively avoid the restore error. Now I just need to code this and try again and see if that works.

1 Like