Login SQL Server and create database name EMS, run below script to generate tables
Ngày đăng:23/11/2022
Trả lời:0
Lượt xem:178
Comments For This Article
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
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.
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
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
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.
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 ['[email protected]+'] 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.
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
+'\'[email protected]
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.
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
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:
@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?
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
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:
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?
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:
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.
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 - 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!!
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)
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:
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 - 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
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,
[email protected] = @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 [email protected] and backup_set_id=(select max(backup_set_id) from msdb..backupset where [email protected] )
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:
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
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 - 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
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 - 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)
Select logicalname,physicalname from migration_lester
OPEN multiple
FETCH NEXT FROM
multiple INTO @multiple,@physical
WHILE(@@FETCH_STATUS = 0)
BEGIN
SET @[email protected]+''''[email protected]+''''+' TO
'+''''[email protected]+''''+','+'MOVE '+''
FETCH NEXT FROM multiple INTO @multiple,@physical
END
CLOSE multiple
DEALLOCATE multiple
SET @restoredb = substring(@restoredb,1,len(@restoredb)-5)
print (@restoredb)
END
[email protected]@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- Run print @restoredb first to view the databases to be restored
-- When ready, run exec (@restoredb)
-- EXEC (@restoredb)
[email protected]@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--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
[email protected]@@@@@@@@@@@@@@@@@@
--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: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 - 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.
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 - 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
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'!!
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ê émuitobem-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 NOTIN('master','model','msdb','tempdb')WHILEEXISTS(SELECTTOP 1 name
FROM [dbo].#tempBackup WHERE flag =
0) BEGIN SELECT @name = name, @fileName
= @path + name +'_'+ @fileDate +'.BAK'-- Unique FileName [email protected] = @path + @name + '.BAK' -- Non-Unique Filename FROM
[dbo].#tempBackup WHERE flag = 0-- does not overwrite the existing
file BACKUPDATABASE @name TODISK= @fileName -- overwrites the existing file (Note: remove @fileDate from the fileName so they are no longer unique --BACKUP DATABASE @name TO DISK = @fileName WITH
INITUPDATE #tempBackup SET flag = 1 WHERE name
= @name AND flag = 0 END
DROP
TABLE [dbo].#tempBackupHere 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 INTIDENTITY(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 NOTIN('master','model','msdb','tempdb')SELECTTOP 1 @backupCount = intID FROM
[dbo].#tempBackup ORDERBY intID DESC
IF
((@backupCount ISNOTNULL)AND(@backupCount >
0)) BEGIN DECLARE @currentBackup INTSET
@currentBackup = 1WHILE(@currentBackup <=
@backupCount) BEGIN SELECT @name =
name, @fileName = @path + name +'_'+ @fileDate +'.BAK'-- Unique
FileName [email protected] = @path + @name + '.BAK' -- Non-Unique Filename FROM
[dbo].#tempBackup WHERE intID = @currentBackup-- does not overwrite the existing
file BACKUPDATABASE @name TODISK= @fileName-- overwrites the existing file (Note: remove @fileDate from the fileName so they are no longer unique --BACKUP DATABASE @name TO DISK = @fileName WITH
INITSET @currentBackup = @currentBackup + 1END 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 [email protected]
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.
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
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
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