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 2008 Forums
 Transact-SQL (2008)
 Adding current date to output backup file
 New Topic  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
Constraint Violating Yak Guru

United Kingdom
451 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
17681 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
Constraint Violating Yak Guru

United Kingdom
451 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
Constraint Violating Yak Guru

United Kingdom
451 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  
 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