Adding current date to output backup file

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 2008 Forums

Transact-SQL (2008)

Adding current date to output backup file

Author

Topic

snejk
Starting Member

3 Posts

Posted - 2014-08-19 : 03:54:26

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 ! :)

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-19 : 04:37:34

You need to add this line to your select statement:

GetDate() as CurrentDate

We are the creators of our own reality!

snejk
Starting Member

3 Posts

Posted - 2014-08-19 : 07:25:35

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 :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-08-19 : 09:11:01

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
[spoiler]Time is always against us[/spoiler]

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-19 : 09:19:20

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!

snejk
Starting Member

3 Posts

Posted - 2014-08-20 : 02:18:17

It still makes new backup file each day :( I only want it to change the date in one backup file :/

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-20 : 04:12:47

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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources