Simple script to backup all SQL Server databases

By:   |   Updated: 2022-07-06   |   Comments (282)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > 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. You could also use a while loop if you prefer not to use a cursor. This is a very straight forward process and you only need a handful of commands to do this.

How to Backup All SQL Server Databases

  1. Specify path to store database backups
  2. Specify backup file name format
  3. Select list of databases to backup
  4. Loop through databases
  5. Programmatically create database backup command using database name, path and file name format
  6. See attached SQL Server backup script

Database Backup File Name Format DBname_YYYYMMDD.BAK

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.

-- source: https://www.MSSQLTips.com

-- https://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/

DECLARE @name NVARCHAR(256) -- database name  
DECLARE @path NVARCHAR(512) -- path for backup files  
DECLARE @fileName NVARCHAR(512) -- filename for backup  
DECLARE @fileDate NVARCHAR(40) -- used for file name
 
-- specify database backup directory
SET @path = 'C:\test\'  
 
-- specify filename format
SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) 
 
DECLARE db_cursor CURSOR READ_ONLY FOR  
SELECT name 
FROM master.sys.databases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
 
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

Database Backup File Name Format DBname_YYYYMMDD_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(NVARCHAR(20),GETDATE(),112)

with this line:

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

Adding Additional Items to Backup Command

To add additional options, such as backup progress (stats) and compression, you can change this line in the script:

BACKUP DATABASE @name TO DISK = @fileName 

with this line:

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

There are plenty of other backup options you can add.

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2022-07-06

Comments For This Article




Tuesday, May 28, 2024 - 9:53:36 AM - Dan Coffey Back To Top (92269)
Thank you very much for this script. Perfect.

Monday, September 25, 2023 - 2:55:57 PM - Greg Robidoux Back To Top (91597)
Hi Anil,

the change assumes that each folder already exists for each database. So if you create the folders ahead of time it should work.

Also, make sure your @path variable has a \ like this "C:\SQL2022\Backup\"

-Greg

Monday, September 25, 2023 - 10:18:26 AM - Anil Gupta Back To Top (91596)
Hello Greg
It worked for the first database, rest of them failed as create a directory with in the first on e.g. C:\SQL2022\BackupSQLDWDiagnostics\DWDiagnostics_20230925.BAK' with error Operating system error 3(The system cannot find the path specified.
I will work on the script may be i am not using my TSQL head too much.
Many thanks
Anil

Monday, September 25, 2023 - 9:51:45 AM - Anil Gupta Back To Top (91595)
Thanks a million. I will try it and update
Anil

Monday, September 25, 2023 - 9:35:04 AM - Greg Robidoux Back To Top (91594)
Hi Anil, if the folders are already created for each database and the folder name matches the database name you could modify this section

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

to

WHILE @@FETCH_STATUS = 0
BEGIN
SET @path = @path + @name + '\'

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

FETCH NEXT FROM db_cursor INTO @name
END

Monday, September 25, 2023 - 7:20:12 AM - Anil Gupta Back To Top (91593)
Hello
Great script , I was wondering if there is way to back up multiple user databases in there own folder. I did use mkdir command but i think i am missing someting.
Many thank in advance
Anil

Thursday, June 29, 2023 - 8:55:58 PM - Cameron Neale Back To Top (91355)
Just a tip, use the FORMAT function whenever you need to format dates as strings:
SELECT @fileDate = FORMAT(GETDATE(),'yyyyMMdd_HHmmss')

Monday, April 17, 2023 - 11:31:05 AM - Marcos Back To Top (91114)
Thank you , very usefull and simple to apply.

Friday, November 18, 2022 - 6:11:07 AM - Rafael Back To Top (90698)
Thank you very much for scripts.

Tuesday, October 11, 2022 - 7:02:42 AM - Greg Robidoux Back To Top (90586)
Hi Chandu,

looks like you are using these scripts: https://ola.hallengren.com/sql-server-backup.html

I am not sure why it is skipping databases based on what you are using. Are all of the databases you are trying to backup online?

-Greg

Tuesday, October 11, 2022 - 2:41:40 AM - Chandu Patel Back To Top (90583)
this was the job script of a scheduled job of my servers, with this in one server its not skipping in DBs but in one server it's skipping few databases for full backup job, Greg Can you help me with this

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d tools -Q "execute [dbo].[DatabaseBackup] @Databases = 'ALL_DATABASES', @Directory = N'G:backuppath\Backup', @BackupType = 'FULL', @Verify = 'Y', @CleanupMode = 'BEFORE_BACKUP', @CleanupTime = 70, @CheckSum = 'Y', @LogToTable = 'Y'" -b

Monday, October 10, 2022 - 8:33:43 PM - Chandu Patel Back To Top (90581)
Hi Greg
It's a job history, even I tried with the same full backup job which run in other server, scripted and executed it in current server which has full backup skipping issues for few database, still its skipping databases, I tried everything I can,
The same script is working fine in other servers but when I execute it in backup issue server it's not working properly, when I checked job history, it's showed that job was successful

Monday, October 10, 2022 - 12:13:12 PM - Greg Robidoux Back To Top (90578)
Hi Chandu,

Can you provide more info on what you mean by "it shows backup job is successful in the history"? Is this the job history or backup history? One other thing, some people suggested changing the cursor type to STATIC, because of this same issue.

Thanks
Greg

Monday, October 10, 2022 - 11:26:43 AM - Chandu Patel Back To Top (90577)
I've a scheduled job for all Database, but still that job skips few DBs backup, even though it shows backup job is successful in the history,
But when I retrigger the job manually "start job at the step" it backups perfectly, what could be the issue,

Friday, July 22, 2022 - 4:04:25 AM - Muhammad Asad Back To Top (90298)
How we Create Database Backup from live server

Wednesday, July 6, 2022 - 3:10:21 PM - Greg Robidoux Back To Top (90236)
Hi Andy,

Good catch. It should be YYYYMMDD. I can't believe this article has been out there since 2006 and you were the first to notice.

I will update the article.

-Greg

Wednesday, July 6, 2022 - 2:51:15 PM - Andy Back To Top (90235)
Why, oh why, would anyone use the date format yyyyddmm? Are you trying to be as obtuse as possible? The iso standard date (yyyy-mm-dd) which avoids the American (mm-dd-yyyy) and British (dd-mm-yyyy) confusion, and you go an invent an American version of that. An additional benefit of yyyy-mm-dd is that the files named as such will be in date order, rather than a useless order.

Monday, December 13, 2021 - 9:47:51 AM - Greg Robidoux Back To Top (89570)
Hi Saiteja,

Check out this article. https://www.mssqltips.com/sqlservertip/4353/export-sql-server-records-into-individual-text-files/

You will need to make a few changes to do table by table, but this might help you.

-Greg

Friday, December 10, 2021 - 5:17:46 PM - Saiteja Back To Top (89565)
DECLARE @PATH nvarchar(128)='[Path]\'
DECLARE @SERVERNAME nvarchar(128)=''
DECLARE @USERNAME nvarchar(128)=''
DECLARE @PASSWORD nvarchar(128)=''

DECLARE @table_name nvarchar(128)
DECLARE @database_name nvarchar(128)
DECLARE @schema_name nvarchar(128)

PRINT 'REM Backup Script ' + CAST(GETDATE() AS nvarchar(30))

DECLARE abc CURSOR FOR
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES I
JOIN sys.tables T ON I.Table_Name = T.Name
JOIN sys.dm_db_partition_stats S ON t.object_id = s.object_id
AND t.type_desc = 'USER_TABLE' AND s.index_id = 1 AND s.row_count >0 ORDER BY t.name collate Latin1_general_BIN2
OPEN abc;
FETCH NEXT FROM abc INTO @database_name, @schema_name, @table_name
WHILE (@@FETCH_STATUS = 0) BEGIN
PRINT 'BCP ' + @database_name + '.' + @schema_name + '.' + @table_name + ' OUT "' + @PATH + @table_name +'.dat" -S "' + @SERVERNAME + '" -U "' + @USERNAME + '" -P "' + @PASSWORD + '" -N'
FETCH NEXT FROM abc INTO @database_name, @schema_name, @table_name;
END;
CLOSE abc;
DEALLOCATE abc;
GO

Friday, December 10, 2021 - 5:10:10 PM - Greg Robidoux Back To Top (89564)
Hi Saiteja,

Are you using SQLCMD for the BAT file?

Could you post your script?

-Greg

Friday, December 10, 2021 - 5:06:47 PM - Saiteja Back To Top (89563)
Trying to do backup by generating bat file from the script

Friday, December 10, 2021 - 4:45:34 PM - Greg Robidoux Back To Top (89562)
Hi Saiteja,

are you trying to do a BACKUP or use BCP?

-Greg

Friday, December 10, 2021 - 3:23:41 PM - Saiteja Back To Top (89561)
While extracting DB i am getting error as BCP is not recognised as an internal or external command,operable program or batch file
Could you please help me with this

Friday, September 24, 2021 - 8:27:51 AM - Greg Robidoux Back To Top (89261)
If you are just using the database name as the backup file, then each backup gets added to the same file. You will need to add FORMAT, INIT to the command as follows. This will initialize the file and overwrite the previous contents.

BACKUP DATABASE @name TO DISK = @fileName WITH FORMAT, INIT

You can check out this info for more backup options: https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver15

Friday, September 24, 2021 - 2:40:08 AM - çağlar can sarıkaya Back To Top (89258)
if the backup filename is same(like just db name) when it is gettig backup if there is same name file, its not deleting old backup file, its stacking with older version.

like you get a back up today and its 100mb then you get another tomorrow, you expect 100 or 101 but it gonna be 200 something. Because its not deleting older one, even the file names are same

Tuesday, July 27, 2021 - 10:51:44 AM - Michael White Back To Top (89055)
Thank you! This script is a great help!!

Wednesday, July 21, 2021 - 12:12:30 PM - Jeff Back To Top (89035)
For instructional purposes this is a good exercise for explaining how to automate backups by T-SQL. However, since this was originally posted, Ola Hallengren's maintenance scripts came on the scene (and dbaTools if you prefer Powershell) and has become near industry standard for automating production databases. Just thought I'd mention these other options that have since become available.

Wednesday, July 7, 2021 - 8:57:45 AM - Jacco Klene Back To Top (88959)
simple and efficient.

Monday, April 5, 2021 - 9:08:05 AM - Neha Back To Top (88491)
Thanks for such a nice explanation

Saturday, December 5, 2020 - 11:09:49 PM - Mohammad Sultan Al Mamun Back To Top (87887)
Thanks a lot for this very valuable time saving tips.

Friday, December 4, 2020 - 10:21:46 AM - AIJAZ ALI Back To Top (87881)
Very simple script. Thanks a lot!

Monday, October 19, 2020 - 10:03:12 AM - Greg Robidoux Back To Top (86658)
Yes it backs up everything in the database, tables, stored procedures, triggers, data, etc.

Sunday, October 18, 2020 - 9:54:04 PM - Kevin Back To Top (86657)
Does it include stored procedures?

Monday, October 5, 2020 - 1:18:44 AM - trupti Back To Top (86595)
Thank you so much Greg,

after adding read cursor my issue is resolved. :)

Friday, October 2, 2020 - 3:04:03 AM - trupti Back To Top (86584)
Hello Greg,

Thank you so much for prompt revert.
I am surprised that on test its working fine. On Prod instance was initially it worked fine.But now its skipping databases also no error in job history. No logs in SQL error log too. Lets see I have added the Read_only cursor in script. I will monitor todays run.

Prod script:-

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 = 'J:\FullBackup\'

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

DECLARE db_cursor CURSOR READ_ONLY FOR

SELECT name

FROM master.dbo.sysdatabases

WHERE name NOT IN ('tempdb')

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

with COPY_ONLY,COMPRESSION

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor



++++++++++++++++++++++++++++++++++++

Test script


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 = 'J:\FullBackupTest\'

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

DECLARE db_cursor CURSOR FOR

SELECT name

FROM master.dbo.sysdatabases

WHERE name NOT IN ('tempdb')

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

with COPY_ONLY,COMPRESSION

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor

Thursday, October 1, 2020 - 12:26:38 PM - Greg Robidoux Back To Top (86579)
Trupti, take a look at the script in the tip that uses a Read Only cursor. Use the script above and then modify the command to add compression and copy only.
-Greg

Thursday, October 1, 2020 - 11:59:47 AM - trupti Back To Top (86578)
Hello Greg,

I am using user script but unfortunately its not taking all user databases I need to take all 51 db backup but with this script it ia backing up 23 dbs only. How can I fix 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 = 'J:\FullBackup\'

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

DECLARE db_cursor CURSOR FOR

SELECT name

FROM master.dbo.sysdatabases

WHERE name NOT IN ('tempdb')

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

with COPY_ONLY,COMPRESSION

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor

Tuesday, August 6, 2019 - 6:17:24 PM - Dean Back To Top (81987)

Just to supplement Fernando Hraste's comment in case you are going through any issues: I've changed the @path variable to full network path (i.e. if you mapped \\100.100.100.1\folder, change your @path to ='\\100.100.100.1\folder') and it worked flawlessly.
The script was acting wonky without this change. Also, some additional scripting might be needed to enable proper dependancies for using cmd commands, but you can easily find already written scripts on the web.


Wednesday, June 12, 2019 - 3:59:44 AM - Lars Nielsen Back To Top (81428)

Thanks for this useful script. A simple point is that 50 characters isn't much for a database name - I got an error "database doens't exist" because the db name was longer than 50 characters.  To fix this change the first line to VARCHAR(255).


Monday, May 20, 2019 - 8:46:24 AM - Fernando Hraste Back To Top (80130)

If you need make the backup into another networklocation, you can use a simple script to map it:

execute xp_cmdshell 'net use \\[ip adress][Folder nme] [password] /User:[domain\username]'

Then, excecute the routine backup


Thursday, January 31, 2019 - 11:47:27 AM - Greg Robidoux Back To Top (78926)

Hi Anshul,

Why are you chaning the database recovery model?  There shouldn't be a need to change it before you run backups.

For Azure, you should be able to change the path to use a URL.  Take a look at this for more info.

https://docs.microsoft.com/en-us/sql/tutorials/lesson-3-write-a-full-database-backup-to-the-windows-azure-blob-storage-service?view=sql-server-2014

https://docs.microsoft.com/en-us/sql/relational-databases/tutorial-use-azure-blob-storage-service-with-sql-server-2016?view=sql-server-2017

-Greg


Thursday, January 31, 2019 - 4:21:21 AM - Anshul Back To Top (78922)

Hi Greg, 

In my scenario, i want to store the backup files to Azure blob. But i am not able to get the correct syntax for it, have tried multiple things. Can you please help here? Waiting for your reply. Thanks.

Please see the lines i added to work on each database- 

 SET @query = 'ALTER DATABASE ['+@dbname+'] SET RECOVERY FULL;

SET @fileName = @path + [@dbname] + @fileDate + '.bak'


Friday, January 11, 2019 - 1:54:14 PM - Greg Robidoux Back To Top (78739)

Hi Dev,

here are some tips to help with deleting old files.

https://www.mssqltips.com/sqlservertip/4499/delete-old-sql-server-backup-files-with-a-stored-procedure/

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

https://www.mssqltips.com/sqlservertip/2726/using-a-powershell-script-to-delete-old-files-for-sql-server/

https://www.mssqltips.com/sqlservertip/3458/automate-sql-server-express-backups-and-purge-old-backups/

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

https://www.mssqltips.com/sqlservertip/1374/clr-function-to-delete-older-backup-and-log-files-in-sql-server/

-Greg


Thursday, January 10, 2019 - 11:55:31 PM - Dev Back To Top (78705)

Thanks Greg for the rapid response... is there any script that automatically deletes the previous .bkp and starts latest backup again?


Thursday, January 10, 2019 - 8:59:44 AM - Greg Robidoux Back To Top (78692)

Hi Dev,

yes this script will create a full backup of all databases excluding any of the databases that you specify that you don't want to back up.

-Greg


Thursday, January 10, 2019 - 4:53:22 AM - Dev Back To Top (78686)

 Did this script takes whole database backup like

example (Inclue all object along with data,  nothing will be skipped ???)


Friday, December 7, 2018 - 7:20:20 PM - Gladson Reis Back To Top (78429)

Perfect !


Friday, November 16, 2018 - 10:48:22 AM - Greg Robidoux Back To Top (78273)

Hi Venkat,

you can just add these items to the above script as follows:

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

-Greg


Friday, November 16, 2018 - 4:12:01 AM - Venkat Back To Top (78271)

 Hi Greg,

Can you please provide the compression and stats=10 to the backup script.


Tuesday, October 23, 2018 - 7:09:44 AM - Dennis Back To Top (78034)

Wohaaa, thanks for that simple but really effective script!

This saves me a lot of time backing up my databases :)

 


Wednesday, August 29, 2018 - 2:13:34 PM - Muhammad Rehan Qadri Back To Top (77338)

When I ran above script I got:

Msg 3201, Level 16, State 1, Line 23

Cannot open backup device 'E:\BackupAllDBsByMRQ\aclc_20180829.BAK'. Operating system error 3(The system cannot find the path specified.).

Thanks to following link which solved the issue:
https://dba.stackexchange.com/questions/21521/taking-backup-of-all-the-databases-in-sql-server/21674


Tuesday, August 28, 2018 - 4:07:40 PM - Nagaraju Kadiyala Back To Top (77326)

 Script needs to create folder with server name under this folder needs create subfolders with database name wise and backups should be created under the database subfolders. can you please provide the script like that. i tried to use below but i wont create ant folders under the shared path. and got below error.

Could you please help on this?

SET @path = '\\XXX112\sqlbackups$\'+ @@SERVERNAME +'\'+@DBname

Error:

Msg 3044, Level 16, State 1, Line 26

Invalid zero-length device name. Reissue the BACKUP statement with a valid device name.

Msg 3013, Level 16, State 1, Line 26

BACKUP DATABASE is terminating abnormally.

Msg 3044, Level 16, State 1, Line 26

Invalid zero-length device name. Reissue the BACKUP statement with a valid device name.

Msg 3013, Level 16, State 1, Line 26

BACKUP DATABASE is terminating abnormally.


Tuesday, August 28, 2018 - 3:41:26 PM - Nagaraju Kadiyala Back To Top (77325)

 Hi

Got below error while executing this script.

Msg 3044, Level 16, State 1, Line 24

Invalid zero-length device name. Reissue the BACKUP statement with a valid device name.


Tuesday, May 22, 2018 - 6:17:03 AM - lek Back To Top (75997)

 

 thanks a lot ka.


Tuesday, May 8, 2018 - 3:56:42 PM - santosh kumar yogi Back To Top (75901)

 

Thanks Greg. Awesome answers. It is working


Saturday, April 21, 2018 - 3:26:07 AM - www4000 Back To Top (75746)

AAAWESOME!!!


Wednesday, April 4, 2018 - 10:28:41 AM - Philip Elder Back To Top (75605)

 Greg,

I ran the backup with defaults to FILE in SQL Management Studio. It's working now, it wasn't working, at least until I ran the backup process manually.

Go figure. :)


Wednesday, April 4, 2018 - 7:00:59 AM - Sheik Ahmed SM Back To Top (75601)

Thanks Greg. Awesome answers. It is working


Tuesday, April 3, 2018 - 8:07:24 AM - Greg Robidoux Back To Top (75587)

Hi Sheik Ahmed,

Try this to get DDMMYYYY format.

SELECT @fileDate = replace(convert(varchar, getdate(),104),'.','')

-Greg


Tuesday, April 3, 2018 - 5:59:30 AM - sheik ahmed sm Back To Top (75586)

I need script backupwith ddmmyyyy. 112 gives yyyymmdd.

 

for example date i need in reverse format.

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

backup current is database name + yyyymmdd

what i need is
database name + ddmmyyyy

Thanks

Monday, April 2, 2018 - 12:48:03 PM - Greg Robidoux Back To Top (75582)

Hi Philip, that is kind of weird that after backing a database manually the script then worked.  Did you do just a simple BACKUP DATABASE command or did you include any options with the backup?

-Greg


Monday, April 2, 2018 - 12:37:39 PM - Philip Elder Back To Top (75581)

Greg,

Thanks for the reply. Yes, the simple query returns a list of all databases running in the instance.

I modified the path to local storage and it ran successfully but no files were produced.

I ran a manual backup against one of the key databases and then the backup query seemed to run just fine. All databases get backed up without issue.

Weird.


Monday, April 2, 2018 - 12:19:32 PM - Greg Robidoux Back To Top (75580)

Hi Philip,

If you run this query, do you get a list of databases returned?

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


Monday, April 2, 2018 - 11:16:40 AM - Philip Elder Back To Top (75578)

Hi,

The script runs successfully with no errors but no file gets produced?


Monday, March 19, 2018 - 12:00:53 PM - Greg Robidoux Back To Top (75464)

Hi pabbhi,

you can just change line in the code above to do copy only backups.

BACKUP DATABASE @name TO DISK = @fileName

to

BACKUP DATABASE @name TO DISK = @fileName WITH COPY_ONLY

-Greg


Monday, March 19, 2018 - 11:18:59 AM - pabbhi Back To Top (75463)

 

 how to take copyonly backup for all databases


Saturday, March 3, 2018 - 7:29:05 AM - Greg Robidoux Back To Top (75341)

Hi RPi80

You could do this without the cursor and use a loop like Dave did in the script below.  Both approaches will work.

The cursor won't damage any data in the database, but the problem you could have with cursors is that data can be updated by other processes and therefore cause an issue with the cursor data (like skipping a database).  This is why this was changed to a read only cursor to make sure the data stays consistent, plus we are not planning on updating the data in the cursor so this is a better option.  Also, since the backups will take some time to complete the read only option makes more sense.  You could also look at other cursor options like static, forward_only, etc.

Take a look at these other articles for more information about how cursors work and the options:

https://sqlperformance.com/2012/09/t-sql-queries/cursor-options

https://sqlperformance.com/2015/09/t-sql-queries/cursor-options-2

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/declare-cursor-transact-sql

-Greg


Saturday, March 3, 2018 - 5:47:04 AM - RPi80 Back To Top (75340)

 

Hi All!

@Greg Robidoux Great script!
I'm not familiar with TSQL as good as You, but this CURSOR (w/out READ_ONLY) option can anyhow damage any data in database while backup script is running? What is the difference between CURSOR and CURSOR READ_ONLY?

What You think about Dave's script (w/out using CURSOR at all)?

 

Robert

 


Thursday, February 15, 2018 - 1:23:31 AM - Ahsan Back To Top (75215)

how can i create a folder datewise through above statements and on that datefolder my backup should be placed

 


Friday, February 9, 2018 - 8:11:46 AM - Greg Robidoux Back To Top (75146)

Hi Ranu,

try converting things to NVARCHAR as follows:

DECLARE @name NVARCHAR(100) -- database name 
DECLARE @path NVARCHAR(512) -- path for backup files 
DECLARE @fileName NVARCHAR(512) -- filename for backup 
DECLARE @fileDate NVARCHAR(40) -- used for file name
 
-- specify database backup directory
SET @path = 'C:\Backup\' 
 
-- specify filename format
SELECT @fileDate = CONVERT(NVARCHAR(40),GETDATE(),112)
 
DECLARE db_cursor CURSOR READ_ONLY 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


Friday, February 9, 2018 - 1:24:54 AM - ranu gupta Back To Top (75142)

I work on same code. But I got a error "Msg 8114, Level 16, State 12, Line 16

Error converting data type varchar to nvarchar." 


Wednesday, January 31, 2018 - 1:33:46 AM - Compressed Back To Top (75064)

Add WITH COMPRESSION to the end of BACKUP -command to ensure your backups doesn't eat all your diskspace.


Sunday, January 21, 2018 - 8:40:26 AM - Greg Robidoux Back To Top (75001)

Hi Santiago,

You can use SQLCMD and run this from Windows Task Scheduler.

Take a look at this tip too as another option: https://www.mssqltips.com/sqlservertip/3458/automate-sql-server-express-backups-and-purge-old-backups/

-Greg


Saturday, January 20, 2018 - 6:35:30 PM - SantiagoE Back To Top (74999)

Works fine with SQL Express 2012. Now, how can use this script to programing into Task Scheduler?


Thursday, December 21, 2017 - 7:36:22 AM - Dayo O Back To Top (74307)

Good one!

 


Tuesday, December 19, 2017 - 9:50:07 AM - Leo Back To Top (74243)

 

Thanks for the script, it really helped!


Monday, August 7, 2017 - 11:13:36 AM - dave Back To Top (64225)

Or if you want to do with out a cursor (and you should):-

 

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 = '\\t2iwgssql09\d$\Backup' 
 
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
 

SELECT @name   = min (name )
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
 -- SELECT @name

WHILE @name IS NOT null
BEGIN  
   SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
  BACKUP DATABASE @name TO DISK = @fileName  WITH STATS = 5

 --SELECT @name
 
 SELECT @name   = min (name )
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
 AND name > @name 
END  

 


Friday, July 21, 2017 - 9:04:08 PM - Arturo Cruz Back To Top (60044)

 

Hi, I need to do a partial backup (only some tables), and if any of these tables have referential integrity, I can do this, I'm dead for days trying to find a solution. Thank you

 


Friday, July 21, 2017 - 5:51:13 PM - Joe Bock Back To Top (60026)

 

Hello - I'll come clean at the beginning...I am not a developer, but have assumed the role of Release Manager and my SQL background is limited.  My goal is to have a script run during a desktop application install that will backup and restore an existing database.

 

I need help with a backup/restore of an existing SQL 2008 Express database to a new instance of SQL 2014 Express.

 

I have scripts that do the backup and restore, but they were previously used with the same instance name.  I want to modify the script below to include the step of restoring to the newly created instance name. 

 

Can someone look at this script and suggest how I can restore the database to a different instance name?

 

 ////////////////////////////

 

set ANSI_NULLS ON

 

set QUOTED_IDENTIFIER ON

 

GO

 

/****** Object:  StoredProcedure [dbo].[proc_restore_Backup]    Script Date: 01/15/2008 10:26:23 ******/

 

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[proc_restore_Backup]') AND type in (N'P', N'PC'))

 

DROP PROCEDURE [dbo].[proc_restore_Backup]

 

GO

 

CREATE PROCEDURE [dbo].[proc_restore_Backup]

 

@Database varchar(50) = '%'

 

AS

 

DECLARE @DBName sysname,

 

        @statement VARCHAR(8000),

 

 SET @DBName = 'ABC',

 

 -- Entering Backup Command.

 

    SET @statement = 'RESTORE DATABASE ' + @DBName +

 

                     ' FROM DISK = ' + '''' + @dbname + '.bak'' WITH STATS = 20'

 

-- Executing

 

   IF @DBName <> ''

 

    BEGIN

 

      PRINT 'restoring Database: ' + @DBName

 

      EXEC (@statement)

 

      PRINT ''

 

    END

 

 ////////////////////////////

 

 

 

Thanks in advance!!



Thursday, June 15, 2017 - 9:42:49 PM - Greg Back To Top (57448)

 Thanks, Greg. This is awesome and just what I need right now as I'm on my first Access project that uses a SQL Server backend.

To anyone interested, I've adapted this to a Stored Procedure which backsup a specific database by changing the WHERE clause. The name of the database is passed as a parameter along with the back up folder (done from my Access front end via VBA and a pass-through query. Here's the sp..

 

CREATE PROCEDURE [dbo].[sp_SYSTEM_FullBackup]

@DBname VARCHAR(50), -- database name  

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

As

BEGIN

SET NOCOUNT ON;/* Turns off row counting */

DECLARE @BAKName VARCHAR(256) -- filename for backup  

DECLARE @fileDate VARCHAR(20) -- used to append Date & Time to Backup filename: @BAKName 

--credit to Greg Robidoux, MSSQLTIPS.com

-- https://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/

-- specify database backup directory

--SET @path = 'C:\Backup\'  -- 

 

-- specify filename format i.e. Date and time...

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

 

DECLARE db_cursor CURSOR READ_ONLY FOR  

SELECT name 

FROM master.dbo.sysdatabases 

WHERE name = @DBname

 

OPEN db_cursor   

FETCH NEXT FROM db_cursor INTO @DBname   

 

WHILE @@FETCH_STATUS = 0   

BEGIN   

   SET @BAKName = @path + @DBname + '_' + @fileDate + '.BAK'  

   BACKUP DATABASE @DBname TO DISK = @BAKName  

 

   FETCH NEXT FROM db_cursor INTO @DBname   

END   

 

 

CLOSE db_cursor   

DEALLOCATE db_cursor

SET NOCOUNT OFF;/* Restores row counting */

END

 

 Kind regards,

Greg Regan


Tuesday, June 6, 2017 - 2:12:55 PM - Greg Robidoux Back To Top (56894)

Hi Mike,

Glad to hear this worked.

As the default, cursors can be updated, so it is possible something else updated the data the cursor was using and through the cursor out of whack.

This is why a lot of people don't like to use cursors and often build their own loops use a temporary table.

I will update the query to add the READ_ONLY to the cursor.

Thanks for testing and the feedback.

-Greg


Tuesday, June 6, 2017 - 2:06:22 PM - Mike Cataldo Back To Top (56893)

Greg,

Thanks for the help, looks like the READ_ONLY option did the trick.  Do you know why this would have an impact?

Mike


Tuesday, June 6, 2017 - 12:45:00 PM - Mike Cataldo Back To Top (56890)

Greg,

Trying the read only option now.

Mike


Tuesday, June 6, 2017 - 12:39:38 PM - Mike Cataldo Back To Top (56888)

Greg,

I modified the script to backup our 2 largest databases, it backed up the first but failed to backup the second?  The job ran for 8 minutes and reported no errors.

Mike 


Tuesday, June 6, 2017 - 10:52:29 AM - Greg Robidoux Back To Top (56883)

Hi Milke,

Another thing you can try is to make the cursor read only.  Sorry I don't have large databases I can test this on.

Change the declare cursor line to this:

DECLARE db_cursor CURSOR READ_ONLY FOR

 


Tuesday, June 6, 2017 - 10:39:19 AM - Greg Robidoux Back To Top (56882)

Hi Mike,

can you add a filter in the WHERE clause and just backup the one large database to see if that works without issue.

-Greg


Tuesday, June 6, 2017 - 10:24:51 AM - Mike Cataldo Back To Top (56881)

Greg,

Setting the Execution Time Out parameter to 7200 had no impact on the results.  It backed up all databases until it encountered one of our largers customers and then just stopped.

Mike


Tuesday, June 6, 2017 - 10:22:33 AM - Mike Cataldo Back To Top (56880)

 

Greg,

Thanks for the quick response.  It will vary by customers but some of our larger backups are between 30 - 40Gb.  I did notice the "Execution Time Out" setting when creating the mainteancne plan, it's set to 0 which I assumed meant NO timeout.  I'm running a test now setting the Execution Time Out to 7200 seconds to see If I can get it to backup every database.

When running as an Agent Job it reports success, no errors logged.  I checked the text log file associated with the job, again, no errors reported.  I did ORDER the results of the cursor so I could see if it continusouly stops at the same large database.

Mike

 


Tuesday, June 6, 2017 - 10:12:35 AM - Greg Robidoux Back To Top (56879)

Hi Mike,

How large are the databases you are trying to backup?  To be honest I did not test with very large databases, so it is possible there is a time out issue.

You could try to get rid of the cursor and do a loop instead to see if that makes a difference, not sure it will but it is worth trying.

To you get any error message or does it just back up some databases and then just stop?  Are you doing this in a query window or running through a SQL Agent job?

Also, you can see the post below from Aaron Nelson about using PowerShell.

-Greg

 

 


Tuesday, June 6, 2017 - 9:39:53 AM - Mike Cataldo Back To Top (56878)

Greg,

I love the simplicity of the script but I see others having issues if the databases are large in size.  I read the posts but don't see an answer for larger databses.  Any help would be greatly appreciated.  I've run the script interactively and as an agent job with the same behavior, it just stops as it encounters larger databases.

Mike


Tuesday, February 7, 2017 - 7:26:54 AM - Aaron Nelson Back To Top (46071)

Just wanted to contribute this in case it helps someone:

This short 3 lines of PowerShell code will backup every database on an entire instance for you:

Get-SqlDatabase -ServerInstance localhost |
Where { $_.Name -ne 'tempdb' } |
Backup-SqlDatabase

If you need to add a few more options, you can do something like this.

<# Backup All Databases and give them all a file name which
    includes the name of the database & datetime stamp. #>
Get-SqlDatabase -ServerInstance localhost |
Where { $_.Name -ne 'tempdb' } | foreach{$_ |
Backup-SqlDatabase -CompressionOption On -BackupFile "$($_.NAME)_db_$(Get-Date -UFormat %Y%m%d%H%M).bak"};


Saturday, December 31, 2016 - 7:22:50 PM - kiranbapuji Back To Top (45073)

Hi Greg,

We are backing up to different files everytime with belwo naming fomat for the backup piece.

 

For example:

dbname_backup_date_time.bkp

upon failure with the error I mentioned in first post, I just give a retry and it went perfect.

So, I would like to know why it is gonna failed every weekend and upon rereun why it is getting success?

 

Thanks for your help.

Regards,

Kiranbapuji.

 

 

 

 

 

 


Wednesday, December 28, 2016 - 11:38:43 AM - Greg Robidoux Back To Top (45057)

Hi Kiran, it looks like the backup file might be corrupt.  I would try to take the backup and restore to see if the restore works.  Also, check to see if you are backing up to the same file each time.  If so, I would try to backup the database to a new file and see if that works.

-Greg


Sunday, December 25, 2016 - 10:56:46 PM - kiran bapuji Back To Top (45047)

Full backup getting failed every weekend with below error:

Task start: 2016-12-25T00:00:11.

Task end: 2016-12-25T02:05:23.

Failed:(-1073548784) Executing the query "declare @backupSetId as int

select @backupSetId =..." failed with the following error: "Damage to the backup set was detected.

VERIFY DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

 

upon re-run , its getting successfull.

 

Could you help me in finding the culprit for the backup issue in the weekend?

 

 


Tuesday, October 4, 2016 - 11:00:48 AM - Greg Robidoux Back To Top (43493)

Amul,

You can also run this using SQLCMD so you can run from a batch file.

Take a look at this: https://msdn.microsoft.com/en-us/library/ms180944.aspx

-Greg


Tuesday, October 4, 2016 - 9:16:19 AM - Amul Pandit Back To Top (43491)

 Hello Greg,

THanks a Lot once again. It worked perfectly in SSMS environment.

I do not want the user to open SSMS. Can this file be run from externally and still the back up be obtained ???

Please help me

 


Tuesday, October 4, 2016 - 8:51:05 AM - Amul Pandit Back To Top (43489)

Can I call this file from VB code?

Tuesday, October 4, 2016 - 8:42:17 AM - Amul Pandit Back To Top (43488)

 Thanks a lot...

CAn it be executed from outside SSMS ? I mean like a batch file or calling it from VB code ?

 


Tuesday, October 4, 2016 - 5:14:15 AM - Greg Robidoux Back To Top (43486)

Hi Amul,

the code can be placed in a query window in SQL Server Management Studio and executed from the query window.

I suggest your try this out on a development instance first, so you can understand what it is doing.

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

 

-Greg


Tuesday, October 4, 2016 - 3:35:11 AM - Amul Pandit Back To Top (43485)

 Hello,

Understood the concept but tell me where should I write the code and whhch command should I run to execute the same????

 


Thursday, July 7, 2016 - 6:27:29 PM - Vishnu Back To Top (41829)

Hi,

can anyone help with singlecode for my problem

suppose i have two to five servers like server1, server2,.....server5, in this all 5 servers having different DB's every db having daily backup jobs now i want a single script for  this five servers all db's backups are running everyday or not,if yes status yes if not failing any backups status No like this.  

 Thanks

Vishnu


Wednesday, June 22, 2016 - 2:37:05 PM - Corey Zamara Back To Top (41743)

Great script, works exactly as expected, everything backed up fine, my question is do you have a script that will look through the directory and attach all the DB's?

I saw you have this one

https://www.mssqltips.com/sqlservertip/1584/auto-generate-sql-server-restore-script-from-backup-files-in-a-directory/

 

But I was wondering if you can just automate the diretory instead of using the name like a wildcard.


Tuesday, June 7, 2016 - 1:52:48 PM - Greg Robidoux Back To Top (41631)

Hi Josh,

I would use this link if you want to integrate it with above script:

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

Read that tip and at the very end of my script add the command line that tip 1618 explains.

Take a look at the article below too. This explains how to enable xp_cmdshell

https://msdn.microsoft.com/en-us/library/ms190693.aspx

 

Offhand, not sure of books but you can check out this one or others on Amazon

https://www.amazon.com/Getting-Started-T-SQL-Introduction-Writing-ebook/dp/B019A45VLO/ref=sr_1_fkmr0_1?ie=UTF8&qid=1465321915&sr=8-1-fkmr0&keywords=writing+t-sql+code


Tuesday, June 7, 2016 - 1:42:16 PM - Josh C Back To Top (41630)

Greg, I am new to SQL scripting, I reviewed your below links and tried to insert delete scripts into the above back up script and had no luck getting it to pass the debugger in SQL Server management studio. Not sure what do do from here.

Any recommendations on books/manuals to help beginers like me learn SQL scripting?

 


Tuesday, June 7, 2016 - 9:48:04 AM - Greg Robidoux Back To Top (41628)

Hi Josh,

take a look at these tips for different ways to delete older backup files:

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

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

https://www.mssqltips.com/sqlservertip/2726/using-a-powershell-script-to-delete-old-files-for-sql-server/

https://www.mssqltips.com/sqlservertip/1374/clr-function-to-delete-older-backup-and-log-files-in-sql-server/

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


Tuesday, June 7, 2016 - 6:39:00 AM - Josh C Back To Top (41625)

The script works great, but i want to be able to delete back ups automatically after say 14 days, what do I need to add to the above script to achieve this? and where does it need to be added?

Also, where in the code can i specify the back up to occur at 10am and 6pm daily?

 


Thursday, April 28, 2016 - 5:56:27 AM - Greg Robidoux Back To Top (41358)

Hi Mango,

Not sure why this is not working.  I tried your script below and this works for me too.

Kind of hard to troubleshoot if it works when you run it in a query window, but not from SQL Agent.

Also, not very helpful that SQL Agent doesn't give you any error messages to troubleshoot.

Another thing you could try is to use SQLCMD and run this from Windows Task Scheduler to see if that works.

 


Thursday, April 28, 2016 - 3:26:25 AM - Magno Back To Top (41356)

Btw, this is the backup script we currently have:

DECLARE @name VARCHAR(50); -- Database name
DECLARE @path VARCHAR(256); -- Path for backup files
DECLARE @databasepath VARCHAR(256); -- Path for backup files
DECLARE @fileName VARCHAR(256); -- Filename for backup
DECLARE @fileDate VARCHAR(200); -- Used for file name
DECLARE @DeleteDateBAK DATETIME = DATEADD(wk,-1,GETDATE()); -- Cutoff date
DECLARE @DeleteDateTRN DATETIME = DATEADD(dd,-2,GETDATE()); -- Cutoff date

-- Path to backups.
SET @path = 'Z:\MSSQLSERVER\';

-- Get date to include in file name.
SELECT @fileDate =  REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20),GETDATE(),120),'-','_'),' ','_'),':','')
SET @fileDate = @fileDate +  '_'
SET @fileDate = @fileDate + (CONVERT(VARCHAR(200),ABS(CONVERT(BIGINT,CONVERT(BINARY(8), NEWID()))) % 10000000))

-- Dynamically get each database on the server.
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb');

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @name;

-- Loop through the list to backup each database.
WHILE @@FETCH_STATUS = 0
BEGIN
      -- Build the path and file name.
      SET @databasepath = @path + @name + N'\';
      SET @fileName = @databasepath + @name + '_backup_' + @fileDate + '.BAK';

      EXEC master.dbo.xp_create_subdir @databasepath

      -- Backup the database.
      BACKUP DATABASE @name TO  DISK = @fileName WITH NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 10;

      -- Loop to the next database.
      FETCH NEXT FROM db_cursor INTO @name;
END

-- Purge old backup files from disk.
EXEC master.sys.xp_delete_file 0,@path,'BAK',@DeleteDateBAK,1;
EXEC master.sys.xp_delete_file 0,@path,'TRN',@DeleteDateTRN,1;

-- Clean up.
CLOSE db_cursor;
DEALLOCATE db_cursor;


Thursday, April 28, 2016 - 3:15:44 AM - Magno Back To Top (41355)

Hi,

 

I created a second job which will only print the result, and runs at the same time as the original job.

Because when I ran it yesterday, everything went OK.

So the backup job only did the first one, the "print" job printed all the different database backup jobs.

I've tried to find some logic in it, but was thusfar unable to find any...

 

-Magno


Wednesday, April 27, 2016 - 7:15:59 AM - Greg Robidoux Back To Top (41349)

Hi Mango,

change this script line to get a list of all the backup commands from:

BACKUP DATABASE @name TO DISK = @fileName

to

PRINT 'BACKUP DATABASE ' + @name + ' TO DISK = ' + @fileName

Then take the output and create a SQL Agent job with just the commands to see if this works or not.  If this works, you can rule out SQL Agent as an issue.

-Greg


Wednesday, April 27, 2016 - 4:32:34 AM - Magno Back To Top (41348)

Hi,

 

It's different versions. going from SQL 2008, 2012 and 2014.

Just checked again this morning, but the semicolon didn't do the trick sadly. The SQL Agent user is however a sysadmin, so it couldn't be a security issue.
I haven't been able to get additional logging out of it. It just stops logging / executing after the first backup job...

One of the customers, we changed the sort order to make sure the LIVE database was always exported, but the naming on the newer customers is somewhat harder, so we couldn't just change the order by clause.

When running interactively, or manually invoking during the day doesn't seem to affect nor give errors, everything runs fine then.


Tuesday, April 26, 2016 - 10:56:35 AM - Greg Robidoux Back To Top (41346)

Hi Mango,

I just tried this with SQL Server 2012 - 11.0.5058.0 (X64) and this worked too. 

There were 9 databases.

-Greg


Tuesday, April 26, 2016 - 10:45:49 AM - Greg Robidoux Back To Top (41345)

Hi Magno,

What version of SQL Server are you using?

I just tried this using SQL Server 2014 - 12.0.2269.0 (X64) and this worked.

If you run this interactively from a query window do you get any error message or does it just stop running?

-Greg


Tuesday, April 26, 2016 - 10:35:22 AM - Magno Back To Top (41344)

Has any else recently noticed that the update stops after the first database?

We implement this script with our customers and have so far had 4 customers where the backups stopped after the first database. The first customer was around a week ago. The last stopped working yesterday.

Anybody know about windows updates that might have caused this.

If we alter the script to only print the @name, it prints everything.
If we add a semicolon after the backup statement, everything seems ok again.


Tuesday, April 5, 2016 - 5:33:29 AM - merc Back To Top (41137)

 

better to include only online databases and selecting them from sys.databases like:

 

SELECT name

FROM sys.databases 

WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases

AND state = 0

 

This is useful for eventually failovered mirrored DBs, so you can have the same t-sql on both servers without having a lot of errors

 

Thanks 

merc

 

 


Monday, March 28, 2016 - 10:37:00 AM - Greg Robidoux Back To Top (41072)

Hi GeorgeH,

Take a look at this tip to see if you can use this to delete older backups.

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

Thanks
Greg

 


Monday, March 28, 2016 - 9:09:11 AM - georgeh Back To Top (41071)

 thanks

how can i add also in thiw scipt the possibility to delete all the old backups (.bak files) older than 3 days

br

 


Thursday, March 24, 2016 - 9:26:34 AM - Greg Robidoux Back To Top (41045)

Hi Deepak,

You can use this command to change the Recovery Model.  Say your database name is MyDatabase the command is

ALTER DATABASE MyDatabase SET RECOVERY FULL

You can check out the backup tutorial for more information: https://www.mssqltips.com/sqlservertutorial/1/sql-server-backup-options-and-commands-tutorial/

Thanks
Greg

 


Thursday, March 24, 2016 - 2:16:52 AM - Deepak Back To Top (41043)

Thanks.

How can I specify backup options like "Recovery Mode", I need to set it to "Full". Can we do it in script?


Wednesday, January 13, 2016 - 3:27:32 PM - Éd Santos Back To Top (40414)

Worked perfectly. Thanks!


Friday, November 27, 2015 - 10:50:15 AM - Angel Ciau Back To Top (39148)

muchas gracias amigo...


Wednesday, November 18, 2015 - 7:46:56 AM - ramesh more Back To Top (39093)

pls give mi database backup script


Sunday, August 30, 2015 - 2:54:54 AM - Jose Marie Bohol Back To Top (38566)

Hello Greg,

Greetings!

Would you mine to include the error handling in the looping statement for sql server version [2005 and above]?

For example:

While @@FETCH_STATUS = 0

Begin try

Begin End

 

Begin catch

End catch


Friday, July 10, 2015 - 4:18:00 AM - Fry Simpson Back To Top (38169)

Thank you


Thursday, July 9, 2015 - 10:08:28 AM - Greg Robidoux Back To Top (38162)

Hi Fry,

see if you can take the concept from this tip to build what you need.

https://www.mssqltips.com/sqlservertip/1584/auto-generate-sql-server-restore-script-from-backup-files-in-a-directory/

You would need to make a few adjustments, but hopefully this gives you a start.  I will try to write another tip to do what you suggest, but not sure when I will get to it.

-Greg


Thursday, July 9, 2015 - 6:17:26 AM - Fry Simpson Back To Top (38161)

Take a look at this tip for the restores: http://www.mssqltips.com/sqlservertip/1243/auto-generate-sql-server-database-restore-scripts/

-Greg

 

I have read that tip, but I understand it deals with restoring one database.

What I would like, instead, is a script which performs the inverse operation of the script in this tip.

The script in this tip, for which I thank you, performs the backup of all databases in one single run. It would be very useful a script which cycles on every *.bak file in the Backup directory and restores the corresponding database. Is it possible?

With my knoledge the only way I know to restore all db is right-click on the DB --> Tasks --> Restore, but I have to redo manually this operation over and over one time for each single DB.


Wednesday, July 1, 2015 - 5:14:50 PM - jonny Back To Top (38100)

Excellent tutorial ...

Thanks !!!

 

 


Thursday, May 28, 2015 - 11:11:22 PM - Kent Back To Top (37314)

Hi Greg,

Thanks for testing it out. I will check more on the TRY..CATCH parameter and see how we can use it in our environment.

 


Thursday, May 28, 2015 - 4:47:58 PM - Greg Robidoux Back To Top (37307)

Hi Kent,

I haven't tried every scenario, but it looks like the process will try to do each database even if one fails.  I did a couple of tests where the folder didn't exist and it failed for each database in the list.  Also tried a database that was offline, this failed but the other databases backed up without issue.

For extra measure you could create a calling stored procedure and also use TRY..CATCH to catch an error and continue on if there is an issue.

-Greg


Thursday, May 28, 2015 - 1:23:09 AM - Calvin Back To Top (37300)

 

Hey Greg, thanks for posting this. As a newbie,, this has saved me a lot of time. much appreciation. 

Calvin 


Wednesday, May 27, 2015 - 3:11:11 AM - Kent Back To Top (37282)

Hi Greg,

Can I know what will happen if one of the database failed to backup? Will the remaining of the database failed to backup as well?

Thank You.

 

 


Monday, May 25, 2015 - 8:08:34 AM - pio11 Back To Top (37273)

First thanks to Greg for this article.

Second: if anybody want  use batch (and use sqlcmd) there is simple example:

sqlcmd -S .\INSERTGT -U sa -P mypass123 -i backup.sql

or You may use query "inline" like this:

sqlcmd -S .\INSERTGT -U sa -P sa -d test55 -s "|" -w 1000 -Q "Select TOP 20 dok_Id,dok_Nr,dok_NrPelny FROM dbo.dok__Dokument"


Monday, May 25, 2015 - 4:29:52 AM - Martin Henning Back To Top (37272)

Brilliant!!  Works 100% with SQL Server 2012.

Thank you!!


Thursday, March 26, 2015 - 10:33:05 AM - Greg Robidoux Back To Top (36716)

Hi Lian,

yes this script should work for SQL Server 2000 and higher versions.

Take a look at this tip for the restores: http://www.mssqltips.com/sqlservertip/1243/auto-generate-sql-server-database-restore-scripts/

-Greg


Wednesday, March 25, 2015 - 9:52:50 PM - Lian Way Back To Top (36702)

Dear Sir,

This is awesome scripts. May I know if this is working for mssql 2000 enterprise edition and mssql 2008 r2?

Besides, I wish to have the restore script too.

 

Thanks in advance.


Saturday, March 7, 2015 - 9:08:20 AM - Dragonfly Back To Top (36465)

AWESOME!!! Thnx!


Tuesday, January 27, 2015 - 6:29:11 AM - Stefano Gioia Back To Top (36066)

Hi, Greg. You might wanna turn that cursor into a STATIC CURSOR to avoid any problems if the data changes while the backups are running.

I had this problem last week (skipping a few databases) and it was solved by changing the cursor syntax.

Best regards.


Wednesday, December 31, 2014 - 8:01:16 AM - Greg Robidoux Back To Top (35800)

Hi jjj,

the issue is probably with how you are referencing your server name

-S.\TIMESAVER

Did you try

-S TIMESAVER


Tuesday, December 30, 2014 - 6:11:28 PM - jjj Back To Top (35790)

 

ENV 2008r2 SqlExpress 2008r2

So I can run  & successfully execute the script from SMSS , but when I try to run it from a batch file (which maybe the issue)

These are teh contents o the batch file

sqlcmd -S.\TIMESERVER -i"c:\SQLscripts\dAILYbACKUP.SQL"

 

AND I have also tried it with -U sa -P sapassword still the same error below

TCP IP and named instance are enabled

 

C:\SQLSCRIPTS>sqlcmd -S.\TIMESERVER -i"c:\SQLscripts\dAILYbACKUP.SQL"
HResult 0xFFFFFFFF, Level 16, State 1
SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFF
FF].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or in
stance-specific error has occurred while establishing a connection to SQL Server
. Server is not found or not accessible. Check if instance name is correct and i
f SQL Server is configured to allow remote connections. For more information see
 SQL Server Books Online..
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

TCP IP ie enabled, named instance enabled

 

 

 

 


Saturday, December 20, 2014 - 8:11:47 AM - Gaurang Bhadani Back To Top (35700)

Thanks for this article

i hepls me very much

thanks a lot


Wednesday, October 29, 2014 - 12:22:42 PM - Greg Robidoux Back To Top (35111)

Hi Peter,

you can use a UNC path instead of the drive letter as long as the SQL Server service account has rights to write to the folder this should work.

As far as creating separate folders for each database, this gets a little tricky with this approach.  You could use xp_cmdshell to create the folders if they do not already exist. 

As far as writing to the folder you would just need to change this line to:

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


Wednesday, October 29, 2014 - 11:43:55 AM - Peter Thompsen Back To Top (35110)

How can I use SET @path = 'C:\Backup\ to specify a network drive? I'm getting an error when using Z:\DB_Backup\ I suppose its becasue the script doesn't have access to the share?

Also, how can I have the script create a folder for each database, instead of having them all in one folder? 


Monday, October 6, 2014 - 8:41:16 AM - Greg Robidoux Back To Top (34842)

Hi Kahled,

You could use SQLCMD and create a batch process to run every day.

Take a look at this tip: http://www.mssqltips.com/sqlservertip/1543/using-sqlcmd-to-execute-multiple-sql-server-scripts/

-Greg


Monday, October 6, 2014 - 5:48:18 AM - Khaled Back To Top (34840)

Hi!

Thanks it work perfectly, but i must do a backup every day, so can i use a batch file (SQLBackup.bat) and put the commande into this file, after i'll use the planified tasks.

thanks for all


Thursday, September 25, 2014 - 1:55:20 AM - Shashikala Back To Top (34707)

Hi,

I am facing one issue with this script.

Job completed successfully,but backup happend only for one database  not for all and no errors found.

Note: Sometimes backup will happen successfully.

What could be the reason here, please help.

 

Thanks

Shashikala


Saturday, September 6, 2014 - 5:24:44 AM - Bharat Back To Top (34406)

Hi Greg,

Its awesome and great working.

Thanks.

Bharat.

 

  

 


Thursday, August 28, 2014 - 11:18:35 AM - Greg Robidoux Back To Top (34320)

Hi Cultti,

glad you got this workding.

Greg


Thursday, August 28, 2014 - 10:28:13 AM - Cultti Back To Top (34316)

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 - 10:13:50 AM - Greg Robidoux Back To Top (34315)

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 - 9:41:34 AM - Cultti Back To Top (34312)

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 - 9:38:38 AM - Cultti Back To Top (34311)

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:11:51 AM - Greg Robidoux Back To Top (34310)

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 - 8:35:12 AM - Cultti Back To Top (34308)

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


Tuesday, June 24, 2014 - 1:45:35 PM - Jon Back To Top (32376)

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

Tuesday, June 24, 2014 - 1:43:06 PM - Jon Back To Top (32375)

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 

Thursday, April 24, 2014 - 9:37:10 AM - Greg Robidoux Back To Top (30500)

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

 

 


Thursday, April 24, 2014 - 5:14:40 AM - Batista Back To Top (30494)

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



Sunday, March 16, 2014 - 5:43:52 AM - Sameh Dewdar Back To Top (29775)

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 


Saturday, March 15, 2014 - 1:08:55 AM - malli Back To Top (29771)

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.


Saturday, March 15, 2014 - 1:00:45 AM - malli Back To Top (29770)

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


Tuesday, March 11, 2014 - 9:20:39 AM - Greg Robidoux Back To Top (29712)

Hi Sameh,

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

Greg


Tuesday, March 11, 2014 - 9:12:08 AM - Sameh Dewdar Back To Top (29711)

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 - 8:55:43 AM - Greg Robidoux Back To Top (29708)

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 - 7:44:47 AM - Sameh Dewdar Back To Top (29707)

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.


Monday, March 10, 2014 - 12:08:29 PM - Greg Robidoux Back To Top (29688)

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.

 


Sunday, March 9, 2014 - 6:38:25 AM - Sameh Dewdar Back To Top (29684)

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


Wednesday, February 26, 2014 - 4:00:43 AM - sadiq Back To Top (29577)

Thanks sir


Tuesday, February 25, 2014 - 11:43:08 AM - Greg Robidoux Back To Top (29567)

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.


Tuesday, February 25, 2014 - 4:20:43 AM - sadiq Back To Top (29562)

i want sql server 2008 R2 tables with data backup script


Thursday, February 20, 2014 - 6:32:00 PM - Greg Robidoux Back To Top (29520)

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.

 

 

 

 


Friday, February 14, 2014 - 7:16:57 AM - Batista Back To Top (29448)

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     


Tuesday, January 7, 2014 - 8:56:30 AM - amg Back To Top (27978)

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.

 


Monday, January 6, 2014 - 6:20:28 AM - PRITESH Back To Top (27965)

Thanks .. Nice Information


Thursday, January 2, 2014 - 3:31:33 PM - Greg Robidoux Back To Top (27940)

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


Friday, December 27, 2013 - 5:58:35 AM - Carinne Back To Top (27892)

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)

 


Friday, December 27, 2013 - 5:31:49 AM - Carinne Back To Top (27891)

 

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)


Monday, December 16, 2013 - 10:28:41 AM - Greg Robidoux Back To Top (27808)

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: https://www.mssqltips.com/sqlservertip/1964/sql-server-2008-backup-compression/


Monday, December 16, 2013 - 7:26:49 AM - Sunil Back To Top (27807)

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 - 7:24:38 AM - Sunil Back To Top (27806)

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

 


Sunday, November 3, 2013 - 11:52:44 AM - Ger Versteeg Back To Top (27377)

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

 


Sunday, November 3, 2013 - 9:27:52 AM - Greg Robidoux Back To Top (27376)

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


Saturday, November 2, 2013 - 3:35:05 PM - Ger Versteeg Back To Top (27375)

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

 


Wednesday, September 25, 2013 - 9:34:07 AM - Greg Robidoux Back To Top (26931)

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


Wednesday, September 25, 2013 - 5:23:09 AM - scribepl Back To Top (26928)

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' 

 


Tuesday, September 10, 2013 - 1:58:58 PM - Stephen Back To Top (26713)

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


Tuesday, September 10, 2013 - 1:49:44 PM - Greg Robidoux Back To Top (26711)

@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:23:22 PM - Stephen Back To Top (26709)

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


Tuesday, September 10, 2013 - 11:50:56 AM - Stephen Back To Top (26708)

@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 - 11:09:31 AM - Kevin Back To Top (26706)

@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 - 10:56:57 AM - Stephen Back To Top (26703)

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, August 27, 2013 - 6:50:45 AM - Gunawan Back To Top (26492)
Thank you Very much , I'm from Indonesian... This Article very Helpfull ..

Monday, August 26, 2013 - 5:50:02 AM - Johannes Back To Top (26479)

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! 


Monday, July 29, 2013 - 11:02:20 AM - Greg Robidoux Back To Top (26048)

@Alvin - you could create multiple 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, July 29, 2013 - 9:48:47 AM - Alvin Back To Top (26046)

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


Wednesday, June 26, 2013 - 10:47:25 AM - Greg Robidoux Back To Top (25575)

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


Wednesday, June 26, 2013 - 2:24:22 AM - NAVEED Back To Top (25568)

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


Tuesday, April 23, 2013 - 3:23:17 PM - Shile Back To Top (23528)

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 : [email protected]

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.

 


Tuesday, April 23, 2013 - 10:36:46 AM - Greg Robidoux Back To Top (23522)

@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 - 10:13:43 AM - Greg Robidoux Back To Top (23520)

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

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

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

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


Tuesday, April 23, 2013 - 10:11:18 AM - Shile Back To Top (23519)

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

Thanks


Tuesday, April 23, 2013 - 9:59:26 AM - Shile Back To Top (23515)

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.


Monday, April 22, 2013 - 3:35:03 PM - Greg Robidoux Back To Top (23493)

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


Monday, April 22, 2013 - 2:45:18 PM - Shile Back To Top (23491)

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 - 2:36:12 PM - Shile Back To Top (23490)

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


Thursday, March 7, 2013 - 9:11:52 AM - Greg Robidoux Back To Top (22633)

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


Thursday, March 7, 2013 - 8:57:07 AM - Sahul Back To Top (22630)

Hi,

Please get me a script for...

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


Tuesday, March 5, 2013 - 11:05:00 AM - sulaak Back To Top (22575)

@ noel

you can retain  database  using  the  command  below

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


Friday, February 15, 2013 - 2:31:09 PM - fergie348 Back To Top (22165)

Thanks guys - really helpful..


Thursday, January 24, 2013 - 9:34:48 AM - Greg Robidoux Back To Top (21680)

@Tahir - do you get any error messages?


Wednesday, January 23, 2013 - 5:21:12 AM - Tahir Hassan Back To Top (21640)

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


Thursday, January 3, 2013 - 3:41:38 AM - Erik Back To Top (21242)

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

http://www.sqlserverbooster.com


Friday, November 16, 2012 - 8:22:06 AM - Greg Robidoux Back To Top (20376)

@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\'


Friday, November 16, 2012 - 2:12:35 AM - Imtiaz Hussain Back To Top (20371)

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


Monday, November 5, 2012 - 1:34:04 AM - Ganeshan Nadarajan Back To Top (20212)

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. 


Wednesday, October 24, 2012 - 8:55:04 AM - Greg Robidoux Back To Top (20069)

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

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

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


Tuesday, October 23, 2012 - 6:06:43 PM - sebastian wolter Back To Top (20060)

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

 

thanks you

 

swolter


Wednesday, October 17, 2012 - 3:39:30 AM - appu Back To Top (19950)

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.


Monday, October 8, 2012 - 9:04:16 AM - Greg Robidoux Back To Top (19819)

@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: https://www.mssqltips.com/sqlservertutorial/1/sql-server-backup-options-and-commands-tutorial/


Sunday, October 7, 2012 - 10:21:02 PM - jay Back To Top (19814)

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

God Bless Us.....

 


Monday, October 1, 2012 - 10:53:44 AM - Pinakin Back To Top (19754)

Thanks for your reply Greg :)


Thanks

 


Thursday, September 27, 2012 - 12:38:01 PM - Greg Robidoux Back To Top (19716)

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


Thursday, September 27, 2012 - 11:50:42 AM - Pinakin Back To Top (19711)

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


Monday, September 17, 2012 - 9:32:32 AM - Sugath Back To Top (19532)

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


Friday, August 31, 2012 - 11:25:50 AM - george Back To Top (19346)

This script is great thanks.

 

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

 

thanks


Friday, August 10, 2012 - 12:04:29 PM - Indrajeet Back To Top (18991)

 

It is awesome script, it so usefull for me.


Sunday, June 24, 2012 - 1:59:12 PM - hamidreza Back To Top (18187)

Hi

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


Monday, May 21, 2012 - 5:37:13 AM - Nishant Back To Top (17564)

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, May 20, 2012 - 11:48:26 AM - Kuldeep Back To Top (17558)

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 !


Friday, April 27, 2012 - 11:32:53 AM - John J MArtinez Back To Top (17159)

Super!


Friday, April 20, 2012 - 3:55:39 PM - Jeff Simpson Back To Top (17025)

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.


Thursday, April 5, 2012 - 3:24:39 AM - Rajesh Back To Top (16781)

Hai,

Thanks a lot. It was really helpful to me

Thnak you so much. 


Saturday, March 24, 2012 - 8:53:30 AM - Greg Robidoux Back To Top (16599)

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


Friday, March 23, 2012 - 10:27:24 PM - Kumar Back To Top (16597)

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


Monday, March 12, 2012 - 12:04:14 PM - Greg Robidoux Back To Top (16349)

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

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

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


Monday, March 12, 2012 - 11:22:34 AM - Noel Back To Top (16348)

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.


Sunday, March 11, 2012 - 3:05:24 PM - Byron Back To Top (16338)

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

Byron


Saturday, March 10, 2012 - 6:26:42 AM - Greg Robidoux Back To Top (16334)

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

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


Friday, March 9, 2012 - 10:09:52 PM - Byron Back To Top (16332)

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


Friday, March 9, 2012 - 9:09:33 AM - Sasha Back To Top (16316)

Hi Greg, thank you very much!


Friday, March 9, 2012 - 8:21:23 AM - Greg Robidoux Back To Top (16313)

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 9, 2012 - 8:18:09 AM - Greg Robidoux Back To Top (16312)

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 9, 2012 - 7:34:15 AM - Sasha Back To Top (16311)

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.


Thursday, March 8, 2012 - 10:38:55 AM - Bob Back To Top (16300)

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


Friday, March 2, 2012 - 10:21:54 PM - Byron Back To Top (16240)

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.


Wednesday, January 18, 2012 - 6:36:31 AM - Upendra Gupta Back To Top (15692)

Thankyou sir..


Thursday, January 5, 2012 - 4:33:01 PM - chico Back To Top (15526)

nm figured it out


Tuesday, January 3, 2012 - 5:46:55 PM - chico Back To Top (15509)

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


Monday, June 27, 2011 - 6:21:38 AM - Nick F Back To Top (14091)

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.


Monday, June 27, 2011 - 6:10:09 AM - yarick123 Back To Top (14090)

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 - 5:53:42 AM - Nick F Back To Top (14089)

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?


Friday, March 18, 2011 - 2:19:25 PM - Greg Robidoux Back To Top (13253)

Você é muito bem-vindos.


Friday, March 18, 2011 - 12:22:57 PM - Guilherme Back To Top (13252)

Obrigado.

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

Att.

Guilherme


Monday, January 10, 2011 - 12:06:13 AM - John Back To Top (12548)

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

Thanks again


Sunday, January 9, 2011 - 11:41:42 PM - John Back To Top (12547)

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


Thursday, September 23, 2010 - 4:48:19 PM - Greg Back To Top (10200)
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'


Thursday, September 23, 2010 - 3:57:01 PM - Evert Back To Top (10199)
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


Saturday, September 4, 2010 - 7:00:58 PM - Atiq Ur Rahman Chaudhary Back To Top (10127)
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, December 10, 2009 - 5:44:46 PM - nirajan Back To Top (4548)

AWESOME !  Thank you so much!


Thursday, December 10, 2009 - 5:08:51 PM - admin Back To Top (4547)

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 - 4:54:29 PM - nirajan Back To Top (4546)

 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?


Tuesday, September 1, 2009 - 9:39:26 PM - manish Back To Top (3985)

Thanks Sullivrp for correcting the script.

 


Wednesday, August 19, 2009 - 2:19:47 PM - sullivrp Back To Top (3921)

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


Thursday, May 28, 2009 - 11:16:53 PM - manish Back To Top (3475)

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 

 

 

 

 

 

 

 

 


Thursday, May 28, 2009 - 2:58:02 PM - mon69 Back To Top (3470)

 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, March 26, 2009 - 5:46:30 AM - yarick123 Back To Top (3080)

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.


Monday, February 16, 2009 - 5:38:34 AM - saariko Back To Top (2774)

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


Friday, February 13, 2009 - 5:12:28 AM - PilotRiaz Back To Top (2763)

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


Thursday, February 12, 2009 - 8:33:06 AM - admin Back To Top (2758)

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.


Thursday, February 12, 2009 - 7:42:22 AM - PilotRiaz Back To Top (2757)

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 - 7:25:15 AM - admin Back To Top (2756)

If the database is setup using multiple 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:

https://www.mssqltips.com/sqlservertip/935/backup-to-multiple-files-for-faster-and-smaller-sql-server-files/

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

https://www.mssqltips.com/sqlservertip/1126/sql-server-backup-and-restore-to-network-drive/


Thursday, February 12, 2009 - 7:17:11 AM - aprato Back To Top (2755)

 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 - 5:58:49 AM - manish Back To Top (2754)

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 - 4:09:07 AM - PilotRiaz Back To Top (2753)

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


Wednesday, December 24, 2008 - 9:56:19 PM - manish Back To Top (2443)

 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, November 20, 2008 - 10:13:04 AM - aprato Back To Top (2239)

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


Thursday, November 20, 2008 - 10:08:38 AM - aprato Back To Top (2238)

 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 - 6:54:43 AM - beckytest Back To Top (2234)

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' 


Tuesday, November 4, 2008 - 7:43:54 AM - aprato Back To Top (2139)

 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.


Tuesday, November 4, 2008 - 5:49:32 AM - JohanA Back To Top (2135)

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.


Friday, October 10, 2008 - 2:26:41 PM - grobido Back To Top (1954)

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


Wednesday, August 13, 2008 - 2:05:01 AM - [email protected] Back To Top (1628)

 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 


Wednesday, July 9, 2008 - 3:17:51 PM - manojsriram Back To Top (1383)

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















get free sql tips
agree to terms