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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

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

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Adding current date to output backup file
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

snejk
Starting Member

3 Posts

Posted - 08/19/2014 :  03:54:26  Show Profile  Reply with Quote
Hi !

I need assistance in making a .sql script that adds current date to output backup file BUT I want it to change that date in the same file instead of making another backup file.

Thanks ! :)

Edited by - snejk on 08/19/2014 04:01:24

sz1
Aged Yak Warrior

United Kingdom
555 Posts

Posted - 08/19/2014 :  04:37:34  Show Profile  Reply with Quote
You need to add this line to your select statement:

GetDate() as CurrentDate

We are the creators of our own reality!
Go to Top of Page

snejk
Starting Member

3 Posts

Posted - 08/19/2014 :  07:25:35  Show Profile  Reply with Quote
Could you implement that into my script?

BACKUP DATABASE [TEST] TO DISK = N'F:\BACKUP SQL 2014\TEST.bak' WITH NOFORMAT, NOINIT, NAME = N'TEST-Full Database

Backup', SKIP, STATS = 10

Thanks :)
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 08/19/2014 :  09:11:01  Show Profile  Reply with Quote
something like this
declare @filename	varchar(100)

select	@filename	= 'F:\BACKUP SQL 2014\TEST' + convert(varchar(10), getdate(), 112) + '.bak'

BACKUP DATABASE [TEST] TO DISK = @filename WITH NOFORMAT, NOINIT, NAME = N'TEST-Full Database Backup', SKIP, STATS = 10



KH
Time is always against us


Edited by - khtan on 08/19/2014 09:11:31
Go to Top of Page

sz1
Aged Yak Warrior

United Kingdom
555 Posts

Posted - 08/19/2014 :  09:19:20  Show Profile  Reply with Quote
This should work also:

declare @f sysname
set @f=N'F:\BACKUP SQL 2014\TEST_'+convert(nvarchar,getdate(),112)+N'.bak'
BACKUP DATABASE [TEST] TO DISK =@f
WITH NOFORMAT, INIT, NAME = N'TEST-Full Database',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

We are the creators of our own reality!
Go to Top of Page

snejk
Starting Member

3 Posts

Posted - 08/20/2014 :  02:18:17  Show Profile  Reply with Quote
It still makes new backup file each day :( I only want it to change the date in one backup file :/
Go to Top of Page

sz1
Aged Yak Warrior

United Kingdom
555 Posts

Posted - 08/20/2014 :  04:12:47  Show Profile  Reply with Quote
So, you want to append to the existing backup each day creating a new file OVER the original backup, if so you need to use NOINIT, that will append a new backup over the existing. File position number will then increase 1,2,3...of course so will your backup size will increase unless you use DIff.

declare @f sysname
set @f=N'F:\BACKUP SQL 2014\TEST_'+convert(nvarchar,getdate(),112)+N'.bak'
BACKUP DATABASE [TEST] TO DISK =@f
WITH NOFORMAT, NOINIT, NAME = N'TEST-Full Database',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO


You can then use Restore HEADERONLY to check your backup positions/ files within the same backup file like:

Restore HEADERONLY FROM DISK = 'F:\BACKUP SQL 2014\TEST'


We are the creators of our own reality!

Edited by - sz1 on 08/20/2014 04:18:09
Go to Top of Page
  Previous Topic Topic Next 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.07 seconds. Powered By: Snitz Forums 2000