Backup Database to a getdate() Name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Backup Database to a getdate() Name

Author

Topic

jdegaetani
Starting Member

9 Posts

Posted - 2013-10-29 : 09:55:01

I'm trying to create a backup using a static name + a formatted getdate().
Any pointers would be appreciated.

Here's my syntax:
BACKUP DATABASE [DEV-database]
FILEGROUP = 'PRIMARY',
FILEGROUP = 'Data Filegroup 1'
TO DISK = 'C:\MSSQL\Backup\DEV-database'+ (select CONVERT(VARCHAR(8), getdate(), 112))+'.bak'
GO

It doesn't like the + signs.

It doesn't like the + signs when I'm trying to restore that backup to a different named data base either.
Here's my syntax:
RESTORE DATABASE Dev_Database_Test
FROM DISK='C:\MSSQL\Backup\DEV-database'+ (select CONVERT(VARCHAR(8), getdate(), 112))+'.bak'
WITH
MOVE 'DEV-Database_Data' TO 'C:\MSSQL\DATA\Dev_Database_Test.mdf',
MOVE 'DEV-Database_1_Data' TO 'C:\MSSQL\DATA\Dev_Database_Test_1.ndf',
MOVE 'DEV-Database_Log' TO 'C:\MSSQL\DATA\Dev_Database_Test.ldf'

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-29 : 10:06:52

You have to make the entire backup command into a string and then use dynamic SQL to execute that - for example like this:

DECLARE @sql NVARCHAR(4000);

SELECT @sql = 
'BACKUP DATABASE [DEV-database]
FILEGROUP = ''PRIMARY'',
FILEGROUP = ''Data Filegroup 1''
TO DISK = ''C:\MSSQL\Backup\DEV-DATABASE'+ CONVERT(VARCHAR(8), getdate(), 112)+'.bak'''
EXEC sp_executesql @sql;

If you use the maintenance plan wizards, they have features to automatically append the date to your backups.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2013-10-29 : 10:27:04

You should be able to get away with using a variable:


USE master
GO
DECLARE @DS nvarchar(512) = 'C:\MSSQL\Backup\DEV-database'
	+ CONVERT(VARCHAR(8), GETDATE(), 112))
	+'.bak'
BACKUP DATABASE [DEV-database]
FILEGROUP = 'PRIMARY',
FILEGROUP = 'Data Filegroup 1'
TO DISK = @DS

jdegaetani
Starting Member

9 Posts

Posted - 2013-10-29 : 11:06:02

Thank you for the advice. Storing the string into a variable works fine. I would love to use a maintenance plan, but this is a SQL express install.

But now to restore I'm getting:
'DEV_Database_Test.mdf' cannot be overwritten. It is being used by database 'DEV_Database_Test'
&
File 'DEV_Data' cannot be restored to 'C:\MSSQL\DATA\DEV_Database_Test.mdf'. Use WITH MOVE to identify a valid location for the file.

Even when I'm setting the database offline & using MOVE...
Here's the syntax:
USE [master]
ALTER DATABASE Dev_Database_Test
SET OFFLINE WITH ROLLBACK IMMEDIATE
GORESTORE DATABASE Dev_Database_Test
FROM DISK='C:\MSSQL\Backup\DEV-database'+ (select CONVERT(VARCHAR(8), getdate(), 112))+'.bak'
WITH
MOVE 'DEV-Database_Data' TO 'C:\MSSQL\DATA\Dev_Database_Test.mdf',
MOVE 'DEV-Database_1_Data' TO 'C:\MSSQL\DATA\Dev_Database_Test_1.ndf',
MOVE 'DEV-Database_Log' TO 'C:\MSSQL\DATA\Dev_Database_Test.ldf'
ALTER DATABASE Dev_Database_Test SET ONLINE
GO
ALTER DATABASE Dev_Database_Test SET MULTI_USER
GO

Ifor
Aged Yak Warrior

700 Posts

Posted - 2013-10-29 : 12:08:09

The following are more flexible than the Maintenance Wizards and can also be used with SQL Express:

http://ola.hallengren.com/

Ifor
Aged Yak Warrior

700 Posts

Posted - 2013-10-29 : 12:16:53

It looks as though Dev_Database_Test already exists so needs to be REPLACEd:


---- To generate MOVEs from dest DB
--SELECT ',MOVE ''' + name + ''' TO ''' + physical_name + ''''
--FROM sys.master_files
--WHERE DB_NAME(database_id) = 'Dev_Database_Test'
--ORDER BY [type], data_space_id
USE master
GO
ALTER DATABASE Dev_Database_Test SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO
DECLARE @DS nvarchar(512) = 'C:\MSSQL\Backup\DEV-database'
	+ CONVERT(VARCHAR(8), GETDATE(), 112))
	+'.bak';
RESTORE DATABASE Dev_Database_Test
FROM DISK = @DS
WITH RECOVERY, REPLACE --, CHECKSUM
	,MOVE 'DEV-Database_Data' TO 'C:\MSSQL\DATA\Dev_Database_Test.mdf'
	,MOVE 'DEV-Database_1_Data' TO 'C:\MSSQL\DATA\Dev_Database_Test_1.ndf'
	,MOVE 'DEV-Database_Log' TO 'C:\MSSQL\DATA\Dev_Database_Test.ldf';
GO
ALTER DATABASE Dev_Database_Test SET MULTI_USER;
ALTER DATABASE Dev_Database_Test SET RECOVERY SIMPLE;
GO
USE Dev_Database_Test
GO
DBCC checkdb
GO
EXEC sp_updatestats
GO

jdegaetani
Starting Member

9 Posts

Posted - 2013-10-29 : 12:18:24

Thanks for the site. Is there anything that can help with my restore? I added the WITH REPLACE option but I'm still not having luck.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2013-10-29 : 12:29:33

Try checking if another database is using one of the files.

You could also try to run the commented out SELECT that generates the MOVEs from the destination DB to see what files it is currently using.

jdegaetani
Starting Member

9 Posts

Posted - 2013-10-29 : 14:59:32

I know that the dev and the test databases have the same logical file names, though the physical file names are different. Is that a problem?

jdegaetani
Starting Member

9 Posts

Posted - 2013-10-29 : 15:27:03

Thank you Ifor & Jamesk for your suggestions. It was having the same logical files names for both databases.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2013-10-30 : 06:30:40

Umm...

I normally have the same logical file names - just different phyisical file names for production, test, dev, train etc