SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Backup Database to a getdate() Name
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jdegaetani
Starting Member

9 Posts

Posted - 10/29/2013 :  09:55:01  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 10/29/2013 :  10:06:52  Show Profile  Reply with Quote
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.
Go to Top of Page

Ifor
Aged Yak Warrior

582 Posts

Posted - 10/29/2013 :  10:27:04  Show Profile  Reply with Quote
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
Go to Top of Page

jdegaetani
Starting Member

9 Posts

Posted - 10/29/2013 :  11:06:02  Show Profile  Reply with Quote
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
Go to Top of Page

Ifor
Aged Yak Warrior

582 Posts

Posted - 10/29/2013 :  12:08:09  Show Profile  Reply with Quote
The following are more flexible than the Maintenance Wizards and can also be used with SQL Express:

http://ola.hallengren.com/
Go to Top of Page

Ifor
Aged Yak Warrior

582 Posts

Posted - 10/29/2013 :  12:16:53  Show Profile  Reply with Quote
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

Go to Top of Page

jdegaetani
Starting Member

9 Posts

Posted - 10/29/2013 :  12:18:24  Show Profile  Reply with Quote
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.
Go to Top of Page

Ifor
Aged Yak Warrior

582 Posts

Posted - 10/29/2013 :  12:29:33  Show Profile  Reply with Quote
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.
Go to Top of Page

jdegaetani
Starting Member

9 Posts

Posted - 10/29/2013 :  14:59:32  Show Profile  Reply with Quote
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?
Go to Top of Page

jdegaetani
Starting Member

9 Posts

Posted - 10/29/2013 :  15:27:03  Show Profile  Reply with Quote
Thank you Ifor & Jamesk for your suggestions. It was having the same logical files names for both databases.
Go to Top of Page

Ifor
Aged Yak Warrior

582 Posts

Posted - 10/30/2013 :  06:30:40  Show Profile  Reply with Quote
Umm...

I normally have the same logical file names - just different phyisical file names for production, test, dev, train etc
Go to Top of Page

lilianjie
Starting Member

USA
3 Posts

Posted - 10/31/2013 :  03:44:46  Show Profile  Reply with Quote
unspammed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000