MSSQLTips

http://www.mssqltips.com/tip.asp?tip=1070

Good Script man, it was very helpfull to me, and i hope it will be very much helpfull to others tooo....

Thanks a lot. I needed the file name also in the same naming convention, it was really helpful to me

.

Thnak you so much.  :)

 After many hundreds of years of searching the net and speaking to SQL X Spurts, a last a backup that I can understand

Thanks so much 

Here is an update to the script to also include the time in the filename.

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

DECLARE db_cursor CURSOR FOR

SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ('test5')

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name

BEGIN

SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor

Hi, Thanks for this script.

 Is there anyway to get this to overwrite the files?

I want to dump every day without date in the filename to put them on tape and not get a bunch of files stacked on the server.

But when the job runs the next day it appends on the previous files and they get double in size.

 If you want to overwrite the backup file, add a WITH INIT to the end of the BACKUP command.  This will overwrite as opposed to appending.

Great script but not sure how to get around the date variable as this creates a new database each time. With INIT as the command to overwrite the database, doesn't help if the database has a different name each time.

Not a sql DBA obviously and if you were to change the following to a static name what syntax would you use? thanks

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 

 I think all you need to do is eliminate the filedate piece

i.e.

from

SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 

to

SET @fileName = @path + @name + '.BAK' 

Greg's script modified for a db that is backed up to a single file name with its contents overwritten would look something like this

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

DECLARE db_cursor CURSOR FOR

SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ('armando')

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0

BEGIN

    SET @fileName = @path + @name + '.BAK'
    BACKUP DATABASE @name TO DISK = @fileName WITH INIT
    FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor
DEALLOCATE db_cursor

 As I learned some where that cursors are not to be used so just updated the main article without cursors.

Yours scipts are superb, very helpful - Thanks.

I was wondering can the script be modified to backup half of the database instead of the whole DB?

I have a large database and i want to backup half of it because of space isues on our hard disks.

Regards, Riaz

Riaz,

How can we justify the half, as you said you want "to backup half of the database instead of the whole DB".

 It sounds like to what you're referring to is archiving data.  Backing up a database just makes a copy of the data.  If you're experiencing data bloat, then you should look into archiving old data.

If the database is setup using mulitiple Files you can do file level backups instead of a full backup.

If you have enough space on other drives you can split the backup into multiple files.

See this tip:

http://www.mssqltips.com/tip.asp?tip=935

You can also do a backup to a network drive.  See this tip:

http://www.mssqltips.com/tip.asp?tip=1126

Our database is 22553.56 MB

I would like to backup a small amount of this, i am not interested in having all the data backed up as space is an issue on our disk.

Could this be chopped in say half?

maybe i should read into archiving and do it this way.