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.
With the use of T-SQL you can generate your backup commands and with the use ofcursors you can cursor through all of your databases to back them up one by one. Youcould also use awhile 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.
VBScript to backup SQL 2000
DOWNLOAD: https://vittuv.com/2vJfpv
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.
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.
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.
@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?
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 directorySET @path = 'C:\Backup\' -- specify filename formatSELECT @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
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 directorySET @path = '\\t2iwgssql09\d$\Backup' -- specify filename formatSELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
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.
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.
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.
DECLARE @name VARCHAR(50); -- Database nameDECLARE @path VARCHAR(256); -- Path for backup filesDECLARE @databasepath VARCHAR(256); -- Path for backup filesDECLARE @fileName VARCHAR(256); -- Filename for backupDECLARE @fileDate VARCHAR(200); -- Used for file nameDECLARE @DeleteDateBAK DATETIME = DATEADD(wk,-1,GETDATE()); -- Cutoff dateDECLARE @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 FORSELECT nameFROM master.sys.databasesWHERE 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 = 0BEGIN -- 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;
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...
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.
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?
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.
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.
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.
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?
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.
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.
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.
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...
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. 2ff7e9595c
Comments