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.

No matter what you do you will need at least one full backup, so there are not a lot of options.

You could look at using this free tool from Idera that does compressed backups.

This could compress the backup up to 90%.

http://www.idera.com/Content/Show68.aspx?CartID=5290

 Another option is to use BCP to export the raw data table by table.

Ah BCP i will have a go at this one and thanks for the link for a free tool i could also use.

Much appreciated.  Thanks ;)

Thank you for this script.

Does anyone knows how I can make this more admin friendly?  I want to have my daily night backup script call this. Is there a way I can integrate zip into this?

Can I have thie proc call part of another script?

thanks

Manish, thanks a lot for the Idea to avoid cursors - I could not understand before, why not the all databases were backed up.

Small improvement: ms does not recommend to use "SET ROWCOUNT ..." effect with "UPDATE" statement as the this behavior will be possibly changed in next versions of mssql. So, I offer to replace

Update #tempbackup set flag=1 WHERE flag=0

with

Update #tempbackup set flag=1 WHERE flag=0 AND name=@name

Regards,
Yarick.

 hi i have tried this script that backs up all databases and i would say that that is really helpful. i have no problem backing up the databases.

 what i would like to know is how do i reinstate the .bak files into the sql server? what tool should i use if i have no access to the sql server enterprise manager?

thanks for any help.

Hi mon69

I don't know about the tools or any thing to restore the database, how ever I used to restore the same using the batch file.

Copy the following script in notepad and save as C:\Restore.bat. and simply on click your databse will be restored.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 osql -e -S DEV1\SQLEXPRESS -U SA -P manish -Q "RESTORE DATABASE MYDB_May29 FROM DISK = 'C:\Temp\MYDB_May29.bak' WITH REPLACE, MOVE 'MYDB' TO 'C:\Database\May29MYDB.mdf', MOVE 'MYDB_log' TO 'C:\Database\May29MYDB.ldf'

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Please Note : the following information need to modify

* DEV1\SQLEXPRESS is the name of the server name

* SA is the user name 

* manish is the password

-----------------------------------------

Let me know if u face any issues 

Just in case people are still following this thread, here is Manish's code which has been corrected per Yarick's catch and SQL deprecations:

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

CREATE TABLE [dbo].#tempBackup (name VARCHAR(200), flag BIT)

SET @path = 'C:\Backup\'

-- Includes the date in the filename
SET @fileDate = CONVERT(VARCHAR(20), GETDATE(), 112)

INSERT INTO [dbo].#tempBackup (name, flag)
SELECT name, 0
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')

-- does not overwrite the existing file
BACKUP DATABASE @name TO DISK = @fileName

-- overwrites the existing file (Note: remove @fileDate from the fileName so they are no longer unique
--BACKUP DATABASE @name TO DISK = @fileName WITH INIT

DROP TABLE [dbo].#tempBackup

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

CREATE TABLE [dbo].#tempBackup (intID INT IDENTITY (1, 1), name VARCHAR(200))

SET @path = 'C:\Backup\'

-- Includes the date in the filename
SET @fileDate = CONVERT(VARCHAR(20), GETDATE(), 112)

INSERT INTO [dbo].#tempBackup (name)
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')

IF ((@backupCount IS NOT NULL) AND (@backupCount > 0))
BEGIN
DECLARE @currentBackup INT

WHILE (@currentBackup <= @backupCount)
BEGIN
SELECT
@name = name,
@fileName = @path + name + '_' + @fileDate + '.BAK' -- Unique FileName
--@fileName = @path + @name + '.BAK' -- Non-Unique Filename
FROM [dbo].#tempBackup
WHERE intID = @currentBackup

-- overwrites the existing file (Note: remove @fileDate from the fileName so they are no longer unique
--BACKUP DATABASE @name TO DISK = @fileName WITH INIT

END
END

DROP TABLE [dbo].#tempBackup

Thanks Sullivrp for correcting the script.

 Very nice and helpful post.  Thank you so much.

 I have a quick question.  Do I also need to backup the SQL Data and Log files in order to be able to restore the database?   Is backing up the database like shown in this article sufficient to restore a database?

Creating a backup using this script which uses the native backup commands is all you need.

This will backup the data from the data file and the active part of the log file.

So when you need to restore your database you would do something like the following:

RESTORE DATABASE [insertDBnameHere] FROM DISK = 'insertPathAndFilenameHere'

AWESOME !  Thank you so much!

Dear Greg and sullivrp

I am geting and error as access denaid as below:

(1 row(s) affected)

Msg 3201, Level 16, State 1, Line 29

Cannot open backup device 'D:\EMS.BAK'. Operating system error 5(Access is denied.).

Msg 3013, Level 16, State 1, Line 29

BACKUP DATABASE is terminating abnormally.

Please help me in this

Thanks Guys, I found the solution which was about adding permissions of SQL Agent user (local) to the backup folder.

Thanks again

Obrigado.

Diretamente do Brasil. O seu script foi muito útil para mim. Obrigado.

Att.

Guilherme

Você é muito bem-vindos.

Just found this thread - very interesting & nice to know that we are doing things properly :)

A quick question - using the cursor script we have found that it appears to skip over databases for no readily apparent reason - does anyone know why this behaviour occurs?

Nick F, right by this reason I use the "no cursor" version of the script.

But I wunder also, why this behaviour occurs.

Thanks for the swift reply :)

We've noticed this behaviour over the last few weeks (we monitor the behaviour of 200+ servers...), and came to the conclusion that it had something to do with the cursor - but can't figure out what! It wouldn't have been so much of an issue if the job would fail when a database is 'skipped'!! <grrr>

Now we're in the process of re-writting the backup jobs (that use this cursor method) and changing over to the temp. table method.

i was wondering, where do you insert a try catch in the backup script above ?

nm figured it out

Thankyou sir..

Gone are the days of needing to manually maintain my scheduled "backup all db" script when databases are added or removed. THANK you all !!!! Great thread.

How to modify this script to also do verification after each database ?

Hello, thank you for the script. It works perfect within single server but when we try to move DB to the other brand new server there are issues with security because the new server doesn't have the same users and logins.

Is there a way to backup logins and relationship between users and logins in order to restore all at the new server?

Thank you so much.

Bob, add this line of code to the script after the BACKUP DATABASE command.  This will do a verify for each backup file that is created.

RESTORE VERIFYONLY FROM DISK = @fileName

Sasha, take a look at this KB article on moving logins between servers:

http://support.microsoft.com/kb/918992

If you follow the above steps in this link the users will be mapped correctly once you restore the databases on the new server.

Hi Greg, thank you very much!

Thanks for this thread.

Is there a way to add a "timeout" (a pause) into the code, so that after each file is backed up, the process pauses for perhaps 5 minutes?

Why? Because I am trying to backup to a SQL "backup device" (server object) that points to a .bak file located in "Amazon Cloud S3 storage".

It seems that the SQL backup process moves too fast, and the remote backup location can't "keep up" (I get "backup file improperly formatted" and "corrupt header" errors).

I know my code is correct, because if I change my SQL "backup device" to point to a local .bak file, everything works great.

Thanks to all of you that contributed!!
Byron

Hi Byron, yes you can use the WAITFOR comand to delay between steps.  See this tip:

http://www.mssqltips.com/sqlservertip/1423/create-delays-in-sql-server-processes-to-mimic-user-input/

Greg, I truly appreciate how you are helping us all!

Byron

Hi

Having used this code for a number of years now, I still have a small problem.

Is is possible to extend the script to delete backups after a certain period of time.

Noel, take a look at these tips to see if you can create a new process or integrate the delete process into this script.

http://www.mssqltips.com/sqlservertip/1324/maintenance-task-to-delete-old-sql-server-backup-files/

http://www.mssqltips.com/sqlservertip/1618/using-the-forfiles-command-to-delete-sql-server-backups/

Hi,

We need to exclude the Snapshot database name from the script as weel as it will fail for Snapshot DB . We can see similar error while executing the script.

Msg 3002, Level 16, State 1, Line 21

Cannot BACKUP or RESTORE a database snapshot.

Msg 3013, Level 16, State 1, Line 21

BACKUP DATABASE is terminating abnormally.

Thanks,

Kumar

Kumar - you can replace this query in the scrip:

SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb') 

with this query which will only backup online databases and also databases that are not snapshots

SELECT name
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb')
and state_desc = 'ONLINE'
and source_database_id IS NULL

Hai,

Thanks a lot. It was really helpful to me

Thnak you so much. 

Here is why some databases are skipped if you use a cursor.

Assume you have two jobs a full backup and a log backup. The full backup starts at 3:15AM. The log backup starts at 4:15 and runs hourly until 2:15AM. The databases have grown and now the full backups take over an hour. Since @@FETCH_STATUS is a global variable, both jobs are going to use it.

The log backup isn't going to back up a database if the full back up is still running. Since the log backups are going to finish faster than the full backup, this is going to cause @@FETCH_STATUS to return -1. The full back up job uses the global @@FETCH_STATUS which is now -1 and ends the loop.

This is why some days there will be full backups and other days there won't. On the servers where the full databases finish before the log back up job starts, all of the databases are probably backed up every day.

It is a matter of timing. In fact, it doesn't even have to be the log backup job. Anything that changes @@FETCH_STATUS could be culprit.

Another example of why cursors are evil.

Super!

Hi,

It was a great script in deed.How do i use this script to automate the backup so that it runs on its own.

Thanks !

Greetings,

In my job i have to create backup everyday by using isql commands in command prompt....these are the commands i used

1)d:

2)cd medical_backup* (*folder name)

3)isql -Usa -P*(username  and password)

4)dump database medical to"d:\medical_backup\medical_date*.dmp" (*current date)

5)go

Could u pls tell me the script so that i can schedule that script and it will run automatically everyday.....

thank you