Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Simple script to backup all SQL Server databases

MSSQLTips author Greg Robidoux By:   |   Read Comments (137)   |   Related Tips: 1 | 2 | 3 | 4 | More > Backup
Problem

Sometimes things that seem complicated are much easier then you think and this is the power of using T-SQL to take care of repetitive tasks.  One of these tasks may be the need to backup all databases on your server.   This is not a big deal if you have a handful of databases, but I have seen several servers where there are 100+ databases on the same instance of SQL Server.  You could use SQL Server Management Studio to backup the databases or even use Maintenance Plans, but using T-SQL is a much simpler and faster approach.

Solution

With the use of T-SQL you can generate your backup commands and with the use of cursors you can cursor through all of your databases to back them up one by one.  This is a very straight forward process and you only need a handful of commands to do this. 

Here is the script that will allow you to backup each database within your instance of SQL Server.  You will need to change the @path to the appropriate backup directory.

File Naming Format DBname_YYYYDDMM.BAK

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

-- specify database backup directory SET @path = 'C:\Backup\'
-- specify filename format SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases
OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0 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

File Naming Format DBname_YYYYDDMM_HHMMSS.BAK

If you want to also include the time in the filename you can replace this line in the above script:

-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

with this line:

-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

Notes

In this script we are bypassing the system databases, but these could easily be included as well.  You could also change this into a stored procedure and pass in a database name or if left NULL it backups all databases.  Any way you choose to use it, this script gives you the starting point to simply backup all of your databases.

Also, if you wanted to bypass some of your user databases you can include them in the NOT IN section as well.

Next Steps
  • Add this script to your toolbox
  • Modify this script and make it a stored procedure to include one or many parameters
  • Enhance the script to use additional BACKUP options


Last Update: 10/15/2012


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Wednesday, July 09, 2008 - 3:17:51 PM - manojsriram Read The Tip

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


Wednesday, August 13, 2008 - 2:05:01 AM - tnwade@gmail.com Read The Tip

 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 


Friday, October 10, 2008 - 2:26:41 PM - grobido Read The Tip

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

WHILE @@FETCH_STATUS = 0

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


Tuesday, November 04, 2008 - 5:49:32 AM - JohanA Read The Tip

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.


Tuesday, November 04, 2008 - 7:43:54 AM - aprato Read The Tip

 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.


Thursday, November 20, 2008 - 6:54:43 AM - beckytest Read The Tip

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' 


Thursday, November 20, 2008 - 10:08:38 AM - aprato Read The Tip

 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' 

 


Thursday, November 20, 2008 - 10:13:04 AM - aprato Read The Tip

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


Wednesday, December 24, 2008 - 9:56:19 PM - manish Read The Tip

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

 

 

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName NVARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
 
SET @path = 'd:\Backup\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
SELECT name,flag=0 into #tempbackup FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb')
set rowcount 1
WHILE (exists(SELECT * FROM #tempbackup WHERE flag=0))
 BEGIN
       Select @name=name from #tempbackup WHERE flag=0
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
       BACKUP DATABASE @name TO DISK = @fileName       
       Update #tempbackup set flag=1 WHERE flag=0
 END 
set rowcount 0
drop table #tempbackup
 
 
Thanks
Manish
 

Thursday, February 12, 2009 - 4:09:07 AM - PilotRiaz Read The Tip

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


Thursday, February 12, 2009 - 5:58:49 AM - manish Read The Tip

Riaz,

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

 

 

 


Thursday, February 12, 2009 - 7:17:11 AM - aprato Read The Tip

 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.


Thursday, February 12, 2009 - 7:25:15 AM - admin Read The Tip

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


Thursday, February 12, 2009 - 7:42:22 AM - PilotRiaz Read The Tip

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.


Thursday, February 12, 2009 - 8:33:06 AM - admin Read The Tip

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.


Friday, February 13, 2009 - 5:12:28 AM - PilotRiaz Read The Tip

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


Monday, February 16, 2009 - 5:38:34 AM - saariko Read The Tip

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


Thursday, March 26, 2009 - 5:46:30 AM - yarick123 Read The Tip

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.


Thursday, May 28, 2009 - 2:58:02 PM - mon69 Read The Tip

 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.


Thursday, May 28, 2009 - 11:16:53 PM - manish Read The Tip

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 

 

 

 

 

 

 

 

 


Wednesday, August 19, 2009 - 2:19:47 PM - sullivrp Read The Tip

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)

-- Includes the date and time in the filename
--SET @fileDate = CONVERT(VARCHAR(20), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108), ':', '')

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

WHILE EXISTS(SELECT TOP 1 name FROM [dbo].#tempBackup WHERE flag = 0)
BEGIN
SELECT
@name = name,
@fileName = @path + name + '_' + @fileDate + '.BAK' -- Unique FileName
--@fileName = @path + @name + '.BAK' -- Non-Unique Filename
FROM [dbo].#tempBackup
WHERE flag = 0

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

UPDATE #tempBackup
SET flag = 1
WHERE name = @name
AND flag = 0
END

DROP TABLE [dbo].#tempBackup

Here is an alternative method using an indentity field on the temp table to prevent unnecessary select / update statements in the while loop!

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)

-- Includes the date and time in the filename
--SET @fileDate = CONVERT(VARCHAR(20), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108), ':', '')

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

SELECT TOP 1 @backupCount = intID FROM [dbo].#tempBackup ORDER BY intID DESC

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

SET @currentBackup = 1

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

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

SET @currentBackup = @currentBackup + 1

END
END

DROP TABLE [dbo].#tempBackup


Tuesday, September 01, 2009 - 9:39:26 PM - manish Read The Tip

Thanks Sullivrp for correcting the script.

 


Thursday, December 10, 2009 - 4:54:29 PM - nirajan Read The Tip

 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?


Thursday, December 10, 2009 - 5:08:51 PM - admin Read The Tip

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'


Thursday, December 10, 2009 - 5:44:46 PM - nirajan Read The Tip

AWESOME !  Thank you so much!


Saturday, September 04, 2010 - 7:00:58 PM - Atiq Ur Rahman Chaudhary Read The Tip
Wonderful,

I need restore script too to restore all backup databases to new clean instance of SQL Server. The explanation is as below:

Question: 
How to backup SQL Server 2005 user databases in SQL script with all data (.sql) and restore this script of all databases on SQL server 2008 


OR 

How to restore all databases backed up through this script into new clean instance of SQL Server (backed up in SQL Server 2005 standard and restore to SQL Server 2008 web edition).


Can someone help me to backup all MS SQL Server 2005 standard user databases into SQL script (single file) and restore all databases with data from single backup script file to SQL server 2008 web edition. 

Symptom: 
Upgrade option for SQL Server standard 2005 to SQL SQL Server 2008 web edition is not supported. I've planned to upgrade SQL Server 2005 standard instance to SQL server 2008 web edition that is not supported and I'm thinking to backup all databases on SQL server 2005 standard into single script file (.sql) install new instance of SQL Server 2008 web edition and restore single script file into new instance. 
Please help me backup all databases into single script file.

OR 

Alternative option is to generate script to restore all databases through your script in new installation of SQL Server 2008 web edition.

Help me please.


Thursday, September 23, 2010 - 3:57:01 PM - Evert Read The Tip
Hi all,

 

Thank you for these useful and easy to understand suggestions and additions. However, I need one more/other...

 

How do I make this script so that it does not break on offline databases, but merely skips them? 

 

 Evert


Thursday, September 23, 2010 - 4:48:19 PM - Greg Read The Tip
You can add this extra line to the WHERE clause to only include databases that are ONLINE.

SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')
AND DATABASEPROPERTYEX(name, 'status') = 'ONLINE'


Sunday, January 09, 2011 - 11:41:42 PM - John Read The Tip

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


Monday, January 10, 2011 - 12:06:13 AM - John Read The Tip

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

Thanks again


Friday, March 18, 2011 - 12:22:57 PM - Guilherme Read The Tip

Obrigado.

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

Att.

Guilherme


Friday, March 18, 2011 - 2:19:25 PM - Greg Robidoux Read The Tip

Você é muito bem-vindos.


Monday, June 27, 2011 - 5:53:42 AM - Nick F Read The Tip

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?


Monday, June 27, 2011 - 6:10:09 AM - yarick123 Read The Tip

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

But I wunder also, why this behaviour occurs.


Monday, June 27, 2011 - 6:21:38 AM - Nick F Read The Tip

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.


Tuesday, January 03, 2012 - 5:46:55 PM - chico Read The Tip

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


Thursday, January 05, 2012 - 4:33:01 PM - chico Read The Tip

nm figured it out


Wednesday, January 18, 2012 - 6:36:31 AM - Upendra Gupta Read The Tip

Thankyou sir..


Friday, March 02, 2012 - 10:21:54 PM - Byron Read The Tip

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.


Thursday, March 08, 2012 - 10:38:55 AM - Bob Read The Tip

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


Friday, March 09, 2012 - 7:34:15 AM - Sasha Read The Tip

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.


Friday, March 09, 2012 - 8:18:09 AM - Greg Robidoux Read The Tip

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


Friday, March 09, 2012 - 8:21:23 AM - Greg Robidoux Read The Tip

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.


Friday, March 09, 2012 - 9:09:33 AM - Sasha Read The Tip

Hi Greg, thank you very much!


Friday, March 09, 2012 - 10:09:52 PM - Byron Read The Tip

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


Saturday, March 10, 2012 - 6:26:42 AM - Greg Robidoux Read The Tip

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/


Sunday, March 11, 2012 - 3:05:24 PM - Byron Read The Tip

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

Byron


Monday, March 12, 2012 - 11:22:34 AM - Noel Read The Tip

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.


Monday, March 12, 2012 - 12:04:14 PM - Greg Robidoux Read The Tip

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/


Friday, March 23, 2012 - 10:27:24 PM - Kumar Read The Tip

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


Saturday, March 24, 2012 - 8:53:30 AM - Greg Robidoux Read The Tip

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


Thursday, April 05, 2012 - 3:24:39 AM - Rajesh Read The Tip

Hai,

Thanks a lot. It was really helpful to me

Thnak you so much. 


Friday, April 20, 2012 - 3:55:39 PM - Jeff Simpson Read The Tip

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.


Friday, April 27, 2012 - 11:32:53 AM - John J MArtinez Read The Tip

Super!


Sunday, May 20, 2012 - 11:48:26 AM - Kuldeep Read The Tip

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 !


Monday, May 21, 2012 - 5:37:13 AM - Nishant Read The Tip

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

 

 


Sunday, June 10, 2012 - 8:15:21 AM - coolansh63 Read The Tip

you may find this article helpful:

Create Backup of Database in Sql Server using script files.


Sunday, June 24, 2012 - 1:59:12 PM - hamidreza Read The Tip

Hi

i want compare sql databse with backup via T-SQL?


Friday, August 10, 2012 - 12:04:29 PM - Indrajeet Read The Tip

 

It is awesome script, it so usefull for me.


Friday, August 31, 2012 - 11:25:50 AM - george Read The Tip

This script is great thanks.

 

how could I schedule this so it does this every Sunday?

 

thanks


Monday, September 17, 2012 - 9:32:32 AM - Sugath Read The Tip

Great script man.... good work... 


Thursday, September 27, 2012 - 11:50:42 AM - Pinakin Read The Tip

Hi Greg,

This script also work in Microsoft SQL Server  2000 - 8.00.2194 (Intel X86)   Apr 20 2006 15:48:56   Copyright (c) 1988-2003 Microsoft Corporation  Desktop Engine on Windows NT 5.2 (Build 3790: Service Pack 2) or not boz I am upgrading from sql server 2000 Desktop Engine to SQL server 2008R and I have around 120 DB's on that server. I need to take backup all databases using this script. or I have take backup one by one database? I not  able to test this script boz I have only SQl Desktop Engine in Pro. so pelase give me advise about this...

 

Thanks,

Pinakin


Thursday, September 27, 2012 - 12:38:01 PM - Greg Robidoux Read The Tip

@Pinakin - yes this should work for SQL 2000 as well.


Monday, October 01, 2012 - 10:53:44 AM - Pinakin Read The Tip

Thanks for your reply Greg :)


Thanks

 


Sunday, October 07, 2012 - 10:21:02 PM - jay Read The Tip

which one is correct for making script of database backup...guide me because im Newbie..thanks for the help!

God Bless Us.....

 


Monday, October 08, 2012 - 9:04:16 AM - Greg Robidoux Read The Tip

@jay - you can just use the script in the tip above.   The scripts in the comments are just variations of the original script.

Also, take a look at this tutorial: http://www.mssqltips.com/sqlservertutorial/1/sql-server-backup-options-and-commands/


Wednesday, October 17, 2012 - 3:39:30 AM - appu Read The Tip

this script is working fine if I execute it manually.when I scheduled it,it skips databases.i am using this script to take backup of master and msdb databases.backup of msdb database is missing when script is scheduled.


Tuesday, October 23, 2012 - 6:06:43 PM - sebastian wolter Read The Tip

how can i put all this code into a job to be run automatically everyday ???

 

thanks you

 

swolter


Wednesday, October 24, 2012 - 8:55:04 AM - Greg Robidoux Read The Tip

@Sebastian - you can create a SQL Agent job and just paste this code into a T-SQL job step.

If you are using SQL Express you can created a Windows Scheduled Task.  Take a look at these tips for more information on creating a backup plan for SQL Server Express:

http://www.mssqltips.com/sqlservertip/1174/scheduling-backups-for-sql-server-2005-express/

http://www.mssqltips.com/sqlservertip/1486/automate-sql-server-express-backups-and-deletion-of-older-backup-files/


Monday, November 05, 2012 - 1:34:04 AM - Ganeshan Nadarajan Read The Tip

With the use of T-SQL you can generate your backup commands and with the use of cursors you can cursor through all of your databases to back them up one by one.  This is a very straight forward process and you only need a handful of commands to do this. 


Friday, November 16, 2012 - 2:12:35 AM - Imtiaz Hussain Read The Tip

How i can backup the Database from server to on a Network PC.


Friday, November 16, 2012 - 8:22:06 AM - Greg Robidoux Read The Tip

@Imtiaz Hussain - you can make the following change in the script to include a Network Share instead.  Just make sure that SQL Server has permissions to write to this Network Share.

so change this

-- specify database backup directory
SET @path = 'C:\Backup\'

to something like this and put in your ServerName and ShareName

 -- specify database backup directory
SET @path = '\\ServerName\ShareName\'


Thursday, January 03, 2013 - 3:41:38 AM - Erik Read The Tip

This is a free tool that can help you too. I developed and is realible.

http://www.sqlserverbooster.com


Wednesday, January 23, 2013 - 5:21:12 AM - Tahir Hassan Read The Tip

i did all the steps and were able to open it sql server but that is not executing the above script 


Thursday, January 24, 2013 - 9:34:48 AM - Greg Robidoux Read The Tip

@Tahir - do you get any error messages?


Friday, February 15, 2013 - 2:31:09 PM - fergie348 Read The Tip

Thanks guys - really helpful..


Tuesday, March 05, 2013 - 11:05:00 AM - sulaak Read The Tip

@ noel

you can retain  database  using  the  command  below

WITH  RETAINDAYS = 14, NOFORMAT, NOINIT,SKIP, REWIND, NOUNLOAD 
RESTORE VERIFYONLY FROM DISK = @fileName


Thursday, March 07, 2013 - 8:57:07 AM - Sahul Read The Tip

Hi,

Please get me a script for...

How to Find Last Backup Time for All Databases in sql server 2005 ?


Thursday, March 07, 2013 - 9:11:52 AM - Greg Robidoux Read The Tip

@Sahul - take a look at this tip: http://www.mssqltips.com/sqlservertip/1596/sql-server-backup-history-analysis/


Monday, April 22, 2013 - 2:36:12 PM - Shile Read The Tip

Hi,

Very useful script here.

I am in a situation where I'd like to backup all production databases(SharePoint2010 & inhouse app dbs) from on server say, SQL1 to another server SQL2 nightly and then restore them to this other server. 

I was gonna use this to do the backups and then this as a maintenace job to restore(haven't totally figured it out the entire process though)

Running the t-sql provided here gives me errors on just the SharePoint configuration dbs(Search_Service_Application_PropertyStoreDB, SharePoint_AdminContent_... and a couple more). 

The backup location is shared on the SQL2 and all other dbs backups got there no problem. 

So, question is...how to fix this error and if there are other ways of achieving my goal?

Thanks


Monday, April 22, 2013 - 2:45:18 PM - Shile Read The Tip

I forgot to include the exact errors...  Database 'SharePoint_AdminContent_c4d97e78...' does not exist. Make sure that the name is entered correctly.

Msg 3013, Level 16, State 1, Line 31

 

Thanks.


Monday, April 22, 2013 - 3:35:03 PM - Greg Robidoux Read The Tip

@Shile - what SQL Server version are you using?  Also, what is the entire name of the database that is failing?  I tried to create some dummy databases based on what you show and it worked fine in SQL 2012.


Tuesday, April 23, 2013 - 9:59:26 AM - Shile Read The Tip

Hi Greg,

So, I found out why it was failing and you were right on the money about the entire name of the database.

The database name (DECLARE @name VARCHAR(50) -- database name) was set to 50 and most of these SharePoint Configuration databases have names longer than 50 characters(the longest have is 80 characters WebAnalyticsServiceApplication_ReportingDB_356b2d44-71e9-4e3e-979d-29242bc36110)  so, I set it to 150 and all works now. 

And on the second part of my question from yesterday, whats the best way to automate this backup and the restore nightly?

Thanks.


Tuesday, April 23, 2013 - 10:11:18 AM - Shile Read The Tip

Also, can full or differential backup options be added this script?

Thanks


Tuesday, April 23, 2013 - 10:13:43 AM - Greg Robidoux Read The Tip

@shile - if you are using SQL Server Standard or Enterprise you can use SQL Server Agent jobs to do the backup and restore.

If you are using SQL Server Express you can setup a Windows Scheduled Task.  Take a look at these tips:

http://www.mssqltips.com/sqlservertip/1486/automate-sql-server-express-backups-and-deletion-of-older-backup-files/

http://www.mssqltips.com/sqlservertip/2842/how-to-automate-sql-server-restores-for-a-test-server/

http://www.mssqltips.com/sqlservertip/1174/scheduling-backups-for-sql-server-2005-express/


Tuesday, April 23, 2013 - 10:36:46 AM - Greg Robidoux Read The Tip

@shile - yes you can do DIFFERENTIAL backups as well.

Just change this line

BACKUP DATABASE @name TO DISK = @fileName

to

BACKUP DATABASE @name TO DISK = @fileName WITH DIFFERENTIAL

See this for more info: http://www.mssqltips.com/sqlservertutorial/9/sql-server-differential-backups/


Tuesday, April 23, 2013 - 3:23:17 PM - Shile Read The Tip

Hi Greg,

Thanks again and btw my machine is a sql2008r2 ent.

I look at those links and im not sure thats how i'd like to acheive this setup. I also found 2 extra scripts that seemed to have been written for earlier versions of sql server and was wondering if you could do any modification to those. Im also not sure whether to ask this here or on your restore tip page.

Restore all Databases in a Directory

 

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CSS_RestoreDir]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[sp_CSS_RestoreDir]

GO

 

/***************************************************************************************/

-- Procedure Name:    sp_CSS_RestoreDir

-- Purpose:           Restore one or many database backups from a single directory.  This script reads all 

--database backups that are found in the @restoreFromDir parameter.

--Any database backup that matches the form %_db_% will be restored to

--the file locations specified in the RestoreTo... parameter(s).  The database

--will be restored to a database name that is based on the database backup

--file name.  For  example Insurance_db_200305212302.BAK will be restored to

--a database named Insurance.  The characters preceeding the '_db_' text determines

--the name.

--

-- Input Parameters: @restoreFromDir - The directory where the database backups are located

--@restoreToDataDir - The directory where the data files (i.e. MDF) will be restored to

--@restoreToLogDir - The directory where the log files (i.e. LDF) will be restored to.  If

--this parameter is not provided then the log files are restored to @restoreToDataDir.

--                   @MatchFileList - set to 'Y' to restore to same directory structure contained in the backup,

--                           also allows for secondary data files 'ndf' to to be in a different dir than mdf files

--                   @DBName - restore just this one database - selects the latest bak file

--  

-- Output Parameters: None

--

-- Return Values:     

--

-- Written By:        Chris Gallelli -- 8/22/03

-- Modified By:       

-- Modifications:     Bruce Canaday -- 10/20/2003

--                       Added optional parameters @MatchFileList and @DBName

--                    Bruce Canaday -- 10/24/2003

--                       Get the db name as the characters LEFT of the right most '_db_' in the bak filenaame

--                       This is to handle databases such as ALIS_DB

--                    Bruce Canaday -- 10/28/2003

--                       When using @MatchFileList = 'Y' attempt to create any directory that doesn't exist

--                    Bruce Canaday -- 11/04/2003

--                       Allow spaces in the @restoreFromDir directory name

--

-- Sample Execution: exec sp_CSS_RestoreDir 'C:\sqldb\sql_backup', 'C:\sqldb\sql_data', 'C:\sqldb\sql_log'

--

-- Alternate Execution: exec sp_CSS_RestoreDir 'C:\sqldb\sql_backup', @MatchFileList = 'Y'

--

-- Reviewed By:       

--

/***************************************************************************************/

 

CREATE proc sp_CSS_RestoreDir 

       @restoreFromDir varchar(255),

@restoreToDataDir varchar(255)= null,

@restoreToLogDir varchar(255) = null,

       @MatchFileList char(1) = 'N',

       @OneDBName varchar(255) = null

as

 

--If a directory for the Log file is not supplied then use the data directory

If @restoreToLogDir is null

set @restoreToLogDir = @restoreToDataDir

 

set nocount on

 

declare @filename         varchar(40),

@cmd              varchar(500), 

@cmd2             varchar(500), 

        @DataName         varchar (255),

@LogName          varchar (255),

        @LogicalName      varchar(255), 

@PhysicalName     varchar(255), 

@Type             varchar(20), 

@FileGroupName    varchar(255), 

@Size             varchar(20), 

@MaxSize          varchar(20),

@restoreToDir     varchar(255),

        @searchName       varchar(255),

@DBName           varchar(255),

        @PhysicalFileName varchar(255) 

 

create table #dirList (filename varchar(100))

create table #filelist (LogicalName varchar(255), PhysicalName varchar(255), Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20) )

 

--Get the list of database backups that are in the restoreFromDir directory

if @OneDBName is null 

   select @cmd = 'dir /b /on "' +@restoreFromDir+ '"'

else

   select @cmd = 'dir /b /o-d /o-g "' +@restoreFromDir+ '"'

 

insert #dirList exec master..xp_cmdshell @cmd  

 

select * from #dirList where filename like '%_db_%' --order by filename

 

if @OneDBName is null 

   declare BakFile_csr cursor for 

 select * from #dirList where filename like '%_db_%bak' order by filename

else

   begin  -- single db, don't order by filename, take default latest date /o-d parm in dir command above

     select @searchName = @OneDBName + '_db_%bak'

     declare BakFile_csr cursor for 

 select top 1 * from #dirList where filename like @searchName

   end

 

open BakFile_csr

fetch BakFile_csr into @filename

 

while @@fetch_status = 0

   begin

       select @cmd = "RESTORE FILELISTONLY FROM disk = '" + @restoreFromDir + "\" + @filename + "'" 

 

       insert #filelist exec ( @cmd )

 

       if @OneDBName is null 

          select @dbName = left(@filename,datalength(@filename) - patindex('%_bd_%',reverse(@filename))-3)

       else

  select @dbName = @OneDBName

 

       select @cmd = "RESTORE DATABASE " + @dbName + 

" FROM DISK = '" + @restoreFromDir + "\" + @filename + "' WITH " 

 

       PRINT '' 

       PRINT 'RESTORING DATABASE ' + @dbName

 

       declare DataFileCursor cursor for  

select LogicalName, PhysicalName, Type, FileGroupName, Size, MaxSize

from #filelist

 

open DataFileCursor

       fetch DataFileCursor into @LogicalName, @PhysicalName, @Type, @FileGroupName, @Size, @MaxSize

 

       while @@fetch_status = 0

          begin

              if @MatchFileList != 'Y'

                 begin  -- RESTORE with MOVE option 

      select @PhysicalFileName = reverse(substring(reverse(rtrim(@PhysicalName)),1,patindex('%\%',reverse(rtrim(@PhysicalName)))-1 )) 

 

       if @Type = 'L'

       select @restoreToDir = @restoreToLogDir

       else

       select @restoreToDir = @restoreToDataDir

       

       select @cmd = @cmd + 

           " MOVE '" + @LogicalName + "' TO '" + @restoreToDir + "\" + @PhysicalFileName + "', " 

                 end

              else

                 begin  -- Match the file list, attempt to create any missing directory

                     select @restoreToDir = left(@PhysicalName,datalength(@PhysicalName) - patindex('%\%',reverse(@PhysicalName)) )

                     select @cmd2 = "if not exist " +@restoreToDir+ " md " +@restoreToDir

                     exec master..xp_cmdshell  @cmd2

                 end

 

              fetch DataFileCursor into @LogicalName, @PhysicalName, @Type, @FileGroupName, @Size, @MaxSize

 

          end  -- DataFileCursor loop

 

close DataFileCursor

   deallocate DataFileCursor

 

       select @cmd = @cmd + ' REPLACE'

       --select @cmd 'command'

       EXEC (@CMD)

 

       truncate table #filelist

 

       fetch BakFile_csr into @filename

 

   end -- BakFile_csr loop

 

close BakFile_csr

deallocate BakFile_csr

 

drop table #dirList

 

return

GO

 

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO

 

Automatically Restores All database .BAK Files

 

/*************************************************************

Script made by : Lester A. Policarpio

Email Address : lpolicarpio2001@yahoo.com

Date Created : September 03, 2007

--=UPDATES=--

 

January 17, 2008

- Solved outputed value of the @restoredb variable

- Solved the "invalid length parameter" issue

 

May 6, 2008

- Removed unused variables

- Add the headeronly command to accept non sql backup format 

 (dbname_db_YYYYMMDDHHMM.BAK)

- Add more comments

 

May 12, 2008

- Accept Backup Files With Multiple NDFs

 

May 23, 2008

- Solved the problem when RESTORE HEADERONLY produces more than 1 value

 

--=LIMITATIONS=--

- This script is tested for backup files ".BAK" only 

-- SCRIPT NEEDS TO BE ALTERED IF BACKUP EXTENSION IS NOT ".BAK"

*************************************************************/

SET NOCOUNT ON

--Drop Tables if it exists in the database

if exists (select name from sysobjects where name = 'migration_lester')

DROP TABLE migration_lester

if exists (select name from sysobjects where name = 'header_lester')

DROP TABLE header_lester

if exists (select name from sysobjects where name = 'cmdshell_lester')

DROP TABLE cmdshell_lester

 

--Create Tables 

--(cmdshell_lester table for the cmdshell command)

--(migration_lester table for the restore filelistonly command)

--(header_lester table for the restore headeronly command)

CREATE TABLE cmdshell_lester( fentry varchar(1000))

 

CREATE TABLE migration_lester(LogicalName varchar(1024),

PhysicalName varchar(4000),type char(1),FileGroupName varchar(50),

size real,MaxSize real)

 

CREATE TABLE header_lester (BackupName varchar(50),

BackupDescription varchar(100),BackupType int,

ExpirationDate nvarchar(50),Compressed int,Position int,

DeviceType int,UserName varchar(30),ServerName varchar(30),

DatabaseName varchar(50),DatabaseVersion int,

DatabaseCreationDate datetime,BackupSize bigint,FirstLsn binary,

LastLsn binary,CheckpointLsn binary,DifferentialBasLsn binary,

BackupStartDate datetime,BackupFinishDate datetime,SortOrder int,

CodePage int,UnicodeLocaleid int,UnicodeComparisonStyle int,

CompatibilityLevel int,SoftwareVendorId int,SoftwareVersionMajor int,

SoftwareVersionMinor int,SoftwareVersionBuild int,

MachineName varchar(50),Flags int,BindingId nvarchar(50),

RecoveryForkId nvarchar(50),Collation nvarchar(50))

 

--Declare Variables

DECLARE @path varchar(1024),@restore varchar(1024)

DECLARE @restoredb varchar(2000),@extension varchar(1024),@newpath_ldf varchar(1024)

DECLARE @pathension varchar(1024),@newpath_mdf varchar(1024),@header varchar(500)

 

--Set Values to the variables

SET @newpath_mdf = 'C:\' --new path wherein you will put the mdf

SET @newpath_ldf = 'D:\' --new path wherein you will put the ldf

SET @path = 'D:\' --Path of the Backup File

SET @extension = 'BAK'

SET @pathension = 'dir /OD '+@Path+'*.'+@Extension

 

--Insert the value of the command shell to the table

INSERT INTO cmdshell_lester exec master..xp_cmdshell @pathension

--Delete non backup files data, delete null values

DELETE FROM cmdshell_lester WHERE FEntry NOT LIKE '%.BAK%' 

DELETE FROM cmdshell_lester WHERE FEntry is NULL

--Create a cursor to scan all backup files needed to generate the restore script

DECLARE @migrate varchar(1024)

DECLARE migrate CURSOR FOR

select substring(FEntry,40,50) as 'FEntry'from cmdshell_lester 

OPEN migrate

FETCH NEXT FROM migrate INTO @migrate

WHILE (@@FETCH_STATUS = 0)BEGIN

--Added feature to get the dbname of the backup file

SET @header = 'RESTORE HEADERONLY FROM DISK = '+''''+@path+@Migrate+''''

INSERT INTO header_lester exec (@header)

--Get the names of the mdf and ldf

set @restore = 'RESTORE FILELISTONLY FROM DISK = '+''''+@path+@migrate+''''

INSERT INTO migration_lester EXEC (@restore)

--Update value of the table to add the new path+mdf/ldf names

UPDATE migration_lester SET physicalname = reverse(physicalname)

UPDATE migration_lester SET physicalname = 

substring(physicalname,1,charindex('\',physicalname)-1)

 

UPDATE migration_lester SET physicalname = @newpath_mdf+reverse(physicalname) where type = 'D'

UPDATE migration_lester SET physicalname = @newpath_ldf+reverse(physicalname) where type = 'L'

--@@@@@@@@@@@@@@@@@@@@

--Set a value to the @restoredb variable to hold the restore database script

IF (select count(*) from migration_lester) = 2

BEGIN

SET @restoredb = 'RESTORE DATABASE '+(select top 1 DatabaseName from header_lester)

+' FROM DISK = '+ ''''+@path+@migrate+''''+' WITH MOVE '+''''

+(select logicalname from migration_lester where type = 'D')+''''

+' TO '+ ''''+( select physicalname from migration_lester WHERE physicalname like '%mdf%')

+''''+', MOVE '+''''+ (select logicalname from migration_lester where type = 'L')

+''''+' TO '+''''+( select physicalname from migration_lester 

WHERE physicalname like '%ldf%')+''''

print (@restoredb) 

END

 

IF (select count(*) from migration_lester) > 2

BEGIN

SET @restoredb = 

'RESTORE DATABASE '+(select top 1 DatabaseName from header_lester)+

' FROM DISK = '+''''+@path+@migrate+''''+'WITH MOVE '

DECLARE @multiple varchar(1000),@physical varchar(1000)

DECLARE multiple CURSOR FOR

Select logicalname,physicalname from migration_lester

OPEN multiple

FETCH NEXT FROM multiple INTO @multiple,@physical

WHILE(@@FETCH_STATUS = 0)

BEGIN

SET @restoredb=@restoredb+''''+@multiple+''''+' TO '+''''+@physical+''''+','+'MOVE '+''

FETCH NEXT FROM multiple INTO @multiple,@physical

END

CLOSE multiple

DEALLOCATE multiple

SET @restoredb = substring(@restoredb,1,len(@restoredb)-5)

print (@restoredb)

END

 

--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

-- Run print @restoredb first to view the databases to be restored

-- When ready, run exec (@restoredb)

-- EXEC (@restoredb)

 

--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

--Clear data inside the tables to give way for the next 

--set of informations to be put in the @restoredb variable 

TRUNCATE TABLE migration_lester

TRUNCATE TABLE header_lester

FETCH NEXT FROM migrate INTO @migrate

END

CLOSE migrate

DEALLOCATE migrate

--@@@@@@@@@@@@@@@@@@@

 

--Drop Tables 

DROP TABLE migration_lester

DROP TABLE cmdshell_lester

DROP TABLE header_lester

 
 
=====================================
 
 
I get errors on this second one ....Msg 213, Level 16, State 7, Line 1
Column name or number of supplied values does not match table definition.

 


Wednesday, June 26, 2013 - 2:24:22 AM - NAVEED Read The Tip

I have run the same script but  no folder created in my C drive... why???


Wednesday, June 26, 2013 - 10:47:25 AM - Greg Robidoux Read The Tip

@Naveed - this script just creates the backup files.  You need to make sure the directory exists before running.


Monday, July 29, 2013 - 9:48:47 AM - Alvin Read The Tip

How about doing the same script but instead having multiple backup files for each database with each execution.

 

i.e

Database1_01.bak

Database1_02.bak

 

Database2_01.bak

Database2_02.bak


Monday, July 29, 2013 - 11:02:20 AM - Greg Robidoux Read The Tip

@Alvin - you could create mulitple backup copies using the mirrored backup option. 

Take a look at this tip:

http://www.mssqltips.com/sqlservertip/1779/mirrored-database-backup-feature-in-sql-server-2005-and-sql-server-2008/


Monday, August 26, 2013 - 5:50:02 AM - Johannes Read The Tip

Hi! Thanks a lot for the script, works great!

I'm planning on running this on a schedule, and if i were to add "
RESTORE VERIFYONLY FROM DISK = @fileName", would there be a way to log any possible errors?

Cheers! 


Tuesday, August 27, 2013 - 6:50:45 AM - Gunawan Read The Tip
Thank you Very much , I'm from Indonesian... This Article very Helpfull ..

Tuesday, September 10, 2013 - 10:56:57 AM - Stephen Read The Tip

This worked like a charm, and it was easy to just paste it into a query window. I am an ametuer when it comes to SQl so thanks a lot everyone who contributed here! 

But I was wanting to make this into a scheduled task using the Server Agent and I'm not quite confident on what steps I should take to do this.  I tried creating a test job yesterday that would run at 2am, but when I checked it this morning I did not see where the job had run, the file had not been placed in the back up location.  

Any tips or leads in the right direction would be awesome! 


Tuesday, September 10, 2013 - 11:09:31 AM - Kevin Read The Tip

@Stephen - you should just be able to paste the code into a SQL Job step and setup a schedule.

 

It looks like the job did not run based on your comments.  Check the job schedule again to make sure you didn't use the current day when setting it up which would have been in the past.


Tuesday, September 10, 2013 - 11:50:56 AM - Stephen Read The Tip

@Kevin - Thanks! I just created it to run at 11:42am and it is currently in progress. This is literally the best thing since sliced bread. Thank you for the quick response!


Tuesday, September 10, 2013 - 1:23:22 PM - Stephen Read The Tip

Could the code posted by sullivrp also be used to backup transaction logs?


Tuesday, September 10, 2013 - 1:49:44 PM - Greg Robidoux Read The Tip

@Stephen - yes you could use that code or use the original code from the tip.

Just change these lines:

SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
BACKUP DATABASE @name TO DISK = @fileName 

To this:

SET @fileName = @path + @name + '_' + @fileDate + '.TRN' 
BACKUP LOG @name TO DISK = @fileName 


Tuesday, September 10, 2013 - 1:58:58 PM - Stephen Read The Tip

@Greg - Thank you very much, just tried it and everything worked well. This page has made my day, from frustrated to cloud nine just like that. 


Wednesday, September 25, 2013 - 5:23:09 AM - scribepl Read The Tip

i want to do backup on network share but i hav this massage:

 

Cannot open backup device '\\plik\backupDB1_20130925.BAK'. Operating system error 53(failed to retrieve text for this error. Reason: 15105).

 

in script i change only this:

-- specify database backup directory
SET @path = 'C:\Backup\'  

to

-- specify database backup directory
SET @path = '\\files\backup' 

 


Wednesday, September 25, 2013 - 9:34:07 AM - Greg Robidoux Read The Tip

What is the share name you are backing up to. You may just need a \ after backup. 


Saturday, November 02, 2013 - 3:35:05 PM - Ger Versteeg Read The Tip

Hello,

I have problem with @name from my database

@name is the full path of the database e.g. 'D:\content\clientname\system\db\databasename'.

How can I strip the path from @name into e.g. in @singleName so I change:

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

Who know a solution for this.

Thanks, Ger

 


Sunday, November 03, 2013 - 9:27:52 AM - Greg Robidoux Read The Tip

@Ger - if you run this query what information is returned?

 

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

 

This should only have the database name not the path.


Sunday, November 03, 2013 - 11:52:44 AM - Ger Versteeg Read The Tip

Hi Greg,

I hope you have a nice Sunday.

Here you see a part of my result:

002
003
AdventureWorks2012
ApresLunch
E:\CONTENT\BOSHOEK\ADMIN\SYSTEM\DB\ENERGIE_BOSHOEK_RATES.MDF
E:\CONTENT\ENERGIEMASTER\ADMIN\SYSTEM\DB\ENERGIE_ENERGIEMASTER_RATES.MDF
Energie_VESTEDA_Rates
Hoecksteijn
TestData

Now you can see what I mean with 'skip the path of the database on E:\'.

Best regards, Ger

PS. sorry for my bad English, I'am from The Netherlands

 


Monday, December 16, 2013 - 7:24:38 AM - Sunil Read The Tip

Hi, Can anyone provide me script for backup of all databases with the Compression option for sql servers 2012 instance.

 


Monday, December 16, 2013 - 7:26:49 AM - Sunil Read The Tip

Is it possible to include the compression option in the script provided by Greg, in this article, if so can someone send me the modified script with compression option.

 

Thanks!


Monday, December 16, 2013 - 10:28:41 AM - Greg Robidoux Read The Tip

Sunil, just change this line:


from
BACKUP DATABASE @name TO DISK = @fileName 

to
BACKUP DATABASE @name TO DISK = @fileName WITH COMPRESSION

see this tip for more info about backup compression: http://www.mssqltips.com/sqlservertip/1964/sql-server-2008-backup-compression/


Friday, December 27, 2013 - 5:31:49 AM - Carinne Read The Tip

 

Hi Greg,

I would like to ask if I want my DB to be compressed and at the same time the DB can be overwrited. How can I combine it?

BACKUP DATABASE @name TO DISK = @fileName WITH INIT  (Currently am using this only)

BACKUP DATABASE @name TO DISK = @fileName WITH COMPRESSION

 

Thank  you.

Carinne (Newbie)


Friday, December 27, 2013 - 5:58:35 AM - Carinne Read The Tip

Forgot to mention that i'm using SQL Server 2008, 64bits

When i tried to run 

BACKUP DATABASE @name TO DISK = @fileName WITH COMPRESSION, INIT, STATS=10

I got the error message as follow:

 

BACKUP DATABASE WITH COMPRESSION is not supported on Standard Edition (64-bit).

Msg 3013, Level 16, State 1, Line 31

BACKUP DATABASE is terminating abnormally.

 

Thank you.

Carinne (Newbie)

 


Thursday, January 02, 2014 - 3:31:33 PM - Greg Robidoux Read The Tip

Hi Carinne, it looks like compression was added in SQL Server 2008 R2 for the standard edition.

http://technet.microsoft.com/en-us/library/bb964719(v=sql.105).aspx


Monday, January 06, 2014 - 6:20:28 AM - PRITESH Read The Tip

Thanks .. Nice Information


Tuesday, January 07, 2014 - 8:56:30 AM - amg Read The Tip

Great article...not sure if this is a good idea or not, but in our environment we store all the backups to a san.

Is it possible to get the script to use either a list of servers from  a command shell osql -L (or better yet, how would I do that internally from the server without xp_cmdshell?), backup all the dbs and point the backups to paths on the san named after the server /dbname?

Could similar be using the list of linked servers?

Thanks very much, this was a great thread.

 


Friday, February 14, 2014 - 7:16:57 AM - Batista Read The Tip

Hi Greg,

I am using the below scipt while taking the backup, but script is throwing error as


Error occured..

Timeout expired. The timeout period elapsed prior to completion of the

operation or the server is not responding. 

The backup or restore was aborted.

==============================================================================

ALTER PROCEDURE [dbo].[CreateDBBackup]  

  @name VARCHAR(50), -- database name    

  @path VARCHAR(256), -- path for backup files    

  @fileDate VARCHAR(20) -- used for file name  

AS  

BEGIN  

 -- SET NOCOUNT ON added to prevent extra result sets from  

 -- interfering with SELECT statements.  

 SET NOCOUNT ON;  

 DECLARE @fileName VARCHAR(256)  

 -- specify filename format

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

   

    BEGIN     

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

       BACKUP DATABASE @name TO DISK = @fileName    

  

END     


Thursday, February 20, 2014 - 6:32:00 PM - Greg Robidoux Read The Tip

Hi Batista,

Does this work for any database?  Did you try to just backup the master database?

You can change your code and comment out the BACKUP DATABASE line and add this line

PRINT @filename

this way you can see what the @filename looks like to make sure it is valid.

 

 

 

 


Tuesday, February 25, 2014 - 4:20:43 AM - sadiq Read The Tip

i want sql server 2008 R2 tables with data backup script


Tuesday, February 25, 2014 - 11:43:08 AM - Greg Robidoux Read The Tip

Hi Sadiq,

there is not a way to backup individual tables with the native SQL Server backup commands.

The above script will work for SQL 2008 R2 and will backup the entire database.


Wednesday, February 26, 2014 - 4:00:43 AM - sadiq Read The Tip

Thanks sir


Sunday, March 09, 2014 - 6:38:25 AM - Sameh Dewdar Read The Tip

Could you please explain how to make the code work as Full , Diff and Log backup 


Monday, March 10, 2014 - 12:08:29 PM - Greg Robidoux Read The Tip

Hi Sameh,

You would just need to change this line to do differentials:

From:

BACKUP DATABASE @name TO DISK = @fileName 

To:

BACKUP DATABASE @name TO DISK = @fileName  WITH DIFFERENTIAL

 

For transaction log backups change this

From:

BACKUP DATABASE @name TO DISK = @fileName 

To:

BACKUP LOG @name TO DISK = @fileName 

 

You might also want to change your file extensions too as follows:

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

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

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

 

You would need to create three different jobs and run these on different schedules.

 


Tuesday, March 11, 2014 - 7:44:47 AM - Sameh Dewdar Read The Tip

Many Thanks Greg , i have another question , the ldf size is large on the DBs [ i have sql 2000 , 2005 , 2008] should i make truncate  after full backup in case i take log backup , if ok , how could i do it via script.


Tuesday, March 11, 2014 - 8:55:43 AM - Greg Robidoux Read The Tip

Hi Sameh,

you should not need to shrink the log file on a regular basis.  This is something you should do as needed.  After your a successful log backup you can shrink your transaction log files using DBCC SHRINKFILE.  You can use DBCC sqlperf(logspace) to see how much of the transaction log is being used before a log backup occurs this way you have an idea of how much to shrink the file.  If you are unsure a good rule would be to have the transaction log about 20-25% of the data file, but this really depends on how large the data files are and also how much space your transactions take between each log backup.


Tuesday, March 11, 2014 - 9:12:08 AM - Sameh Dewdar Read The Tip

thanks again for your help , 

 

i have another question not related to this topic but it's very urgent , I lose a DB due to RAID failure , i don't have mdf or backup I just have _log.ldf so what you suggest 


Tuesday, March 11, 2014 - 9:20:39 AM - Greg Robidoux Read The Tip

Hi Sameh,

you are pretty much out of luck if you don't have a good MDF data file or a good backup.

Greg


Saturday, March 15, 2014 - 1:00:45 AM - malli Read The Tip

sql server 2000 version  old backups removed without using shrinke file db script , means only truncate using for the  daily backups in server 2000.


Saturday, March 15, 2014 - 1:08:55 AM - malli Read The Tip

sql server 2000 version  old backups removed without using shrinke file db script , means only truncate using for the  daily backups in server 2000, please provide the script in 2000.


Sunday, March 16, 2014 - 5:43:52 AM - Sameh Dewdar Read The Tip

I face another issue with the script 

I try to making log backup 4 time per day using NOINIT option but every time it override the exist one , I think as it with same name I try to change on file name to be

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),108)   but it always give me error , any idea 


Thursday, April 24, 2014 - 5:14:40 AM - Batista Read The Tip

Hi Greg,

If the size of the database is small then there is no issue but once I try to take a fatty DB backup then my application is throwing an error message as shown below. I haven't tried to take backup of master database. There is no issue with filename.

I used get the below error message whenever I tried to take backup of database having size more than 2 - 3 GB.

 

Error occured..

Timeout expired. The timeout period elapsed prior to completion of the

operation or the server is not responding. 

The backup or restore was aborted



Thursday, April 24, 2014 - 9:37:10 AM - Greg Robidoux Read The Tip

Hi Batista, are you using this script in a query window to do the backup or the SQL Server Management Studio backup GUI?

 

 


Tuesday, June 24, 2014 - 1:43:06 PM - Jon Read The Tip

Thanks Greg and others for this very useful script. I've modified it to fit my purposes, specifically:

1. Include the 'Machinename' in the output file --e.g.  SQLServer1-master.bak

2. Added some options to the backup process

3. Add a verify stage into the backup loop 

 
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\' + CAST(SERVERPROPERTY('MachineName') as nvarchar(64)) + '-'

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

-- Includes the date and time in the filename
--SET @fileDate = CONVERT(VARCHAR(20), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108), ':', '')

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

SELECT TOP 1 @backupCount = intID FROM [dbo].#tempBackup ORDER BY intID DESC
IF ((@backupCount IS NOT NULL) AND (@backupCount > 0))
BEGIN
DECLARE @currentBackup INT

SET @currentBackup = 1
WHILE (@currentBackup 

Tuesday, June 24, 2014 - 1:45:35 PM - Jon Read The Tip

For some reason it cut off the second part of the script, so I'm posting again:

 

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\' + CAST(SERVERPROPERTY('MachineName') as nvarchar(64)) + '-'

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

-- Includes the date and time in the filename
--SET @fileDate = CONVERT(VARCHAR(20), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108), ':', '')

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

SELECT TOP 1 @backupCount = intID FROM [dbo].#tempBackup ORDER BY intID DESC
IF ((@backupCount IS NOT NULL) AND (@backupCount > 0))
BEGIN
DECLARE @currentBackup INT

SET @currentBackup = 1
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

-- does not overwrite the existing file
BACKUP DATABASE @name TO DISK = @fileName WITH  RETAINDAYS = 30, NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
-- overwrites the existing file (Note: remove @fileDate from the fileName so they are no longer unique
--BACKUP DATABASE @name TO DISK = @fileName WITH INIT

--Verify each database after it is backed up
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=@name and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=@name )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database @name not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = @fileName WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND

SET @currentBackup = @currentBackup + 1
END
END

DROP TABLE [dbo].#tempBackup

Thursday, August 28, 2014 - 8:35:12 AM - Cultti Read The Tip

Any idea why script backups only first 6 databases when I have total 11 databases online?  Bellow is my script. When I test the SELECT clause the result shows all 11 databases. However, last database that I backup takes 231 seconds. Is there possible timeout in jobs?

 

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

 

 

-- specify database backup directory

SET @path = 'E:\Databases\Backups\'  

 

 

-- specify filename format

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

 

 

DECLARE db_cursor CURSOR FOR  

SELECT name

FROM master.dbo.sysdatabases

WHERE name NOT IN ('master','model','msdb','tempdb')

AND DATABASEPROPERTYEX(name, 'status') != 'OFFLINE'

 

OPEN db_cursor   

FETCH NEXT FROM db_cursor INTO @name   

 

 

WHILE @@FETCH_STATUS = 0   

BEGIN   

       SET @fileName = @path + @name + '_' + @fileDate + '.full.bak'  

       BACKUP DATABASE @name TO DISK = @fileName  

 

 

       FETCH NEXT FROM db_cursor INTO @name   

END   

 

 

CLOSE db_cursor   

DEALLOCATE db_cursor


Thursday, August 28, 2014 - 9:11:51 AM - Greg Robidoux Read The Tip

Hi Cultti,

what is the status of the databases when you run this command.  Are they all ONLINE?

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

 

Also, do any of your databases have spaces in the names?


Thursday, August 28, 2014 - 9:38:38 AM - Cultti Read The Tip

Hi Greg Robidoux,

 

Every database is online. When I do query with:

SELECT name

FROM master.dbo.sysdatabases

WHERE name NOT IN ('master','model','msdb','tempdb')

AND DATABASEPROPERTYEX(name, 'status') != 'OFFLINE'

11 databases are listed in the result. There are no spaces in the names.

I also run exact same query but with attribute "WITH  DIFFERENTIAL" in backup cmd, there is no problem and every database diff file is generated.


Thursday, August 28, 2014 - 9:41:34 AM - Cultti Read The Tip

Sorry, I did not notice the sql that you gave me. Every database is online except one, it is recovery mode(dont know why). Can this cause problems?


Thursday, August 28, 2014 - 10:13:50 AM - Greg Robidoux Read The Tip

Hi Cultti,

Are the databases really large?  I am not sure why it would not work.  It is possible there is a query timeout.

Can you test the 5 databases that did not work and hardcode them into the list of databases to backup:

WHERE name IN ('Db1','Db2','Db3','Db4','Db5')  -- include these databases

 

 


Thursday, August 28, 2014 - 10:28:13 AM - Cultti Read The Tip

Hi Greg,

 

I was able to backup those databases that did not previously backup.

 

The largest succesfully backed database is 15Gb in size.


Thursday, August 28, 2014 - 11:18:35 AM - Greg Robidoux Read The Tip

Hi Cultti,

glad you got this workding.

Greg



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.