May 25, 2017 at 12:19 pm
All, I have gotten this backup script from the web. It seems to work but it is overwriting to the existing backup file. How do I change the code so it creates a new file instead of overwriting to the existing backup file.DECLARE @name VARCHAR(50) -- database name
DECLARE @fileName NVARCHAR(256) -- filename for backup
DECLARE @fileDate NVARCHAR(20) -- used for file name
DECLARE @instanceBackupDir nvarchar(2048)
EXEC [master].[dbo].xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory',
@instanceBackupDir OUTPUT
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @instanceBackupDir + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName With Compression
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
May 25, 2017 at 12:32 pm
That should create a new backup for DAILY, since you have the @fileDate portion in the filename.
@fileDate is set to be a yyyymmdd format, so you'll be writing to the same file should you run it multiple times on the same day.
If you want to create multiples on the same day, change: @filedate = getdate(), then it will generate one file per run.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
May 25, 2017 at 12:51 pm
Henrico is on the right track, but just using a getdate() will cause problems as the colons in the time are invalid characters in a filename.
Instead, try the following:SELECT @fileDate = convert(varchar(20), getdate(), 112) + '_' + replace(convert(varchar(20), getdate(), 114), ':', '')
This will give you the date and the time, with no colons, so if you run your backup multiple times a day, each file will have a different timestamp, down to the thousandths of a second.
May 25, 2017 at 12:54 pm
jasona.work - Thursday, May 25, 2017 12:51 PMHenrico is on the right track, but just using a getdate() will cause problems as the colons in the time are invalid characters in a filename.
Instead, try the following:SELECT @fileDate = convert(varchar(20), getdate(), 112) + '_' + replace(convert(varchar(20), getdate(), 114), ':', '')
This will give you the date and the time, with no colons, so if you run your backup multiple times a day, each file will have a different timestamp, down to the thousandths of a second.
Thanks Jason, didn't test it etc, just gave him a hint and pointer.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
May 25, 2017 at 1:45 pm
Thanks Henrico and Jasona. It is working as expected. Next step for me to either find a script or modify the existing script which would take the diff backup every day and if the full backup doesn't exist, take one instead. Going to be challenging since I am new to SQL Server.
May 25, 2017 at 1:47 pm
Also, is there any book or courses that someone can recommend where I can learn advanced tsql programming. I have learn from w3school but this SELECT @fileDate = convert(varchar(20), getdate(), 112) + '_' + replace(convert(varchar(20), getdate(), 114), ':', '') I would have never figured out on my own. But I would like to learn it eventually.
May 25, 2017 at 2:10 pm
Question, why aren't you using the perfectly good SQL Maintenance plans to perform all of this?
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
May 25, 2017 at 2:16 pm
newdba2017 - Thursday, May 25, 2017 1:47 PMAlso, is there any book or courses that someone can recommend where I can learn advanced tsql programming. I have learn from w3school but thisSELECT @fileDate = convert(varchar(20), getdate(), 112) + '_' + replace(convert(varchar(20), getdate(), 114), ':', '')I would have never figured out on my own. But I would like to learn it eventually.
I'll take a stab at teaching 🙂
SELECT @fileDate = --setting your variable
convert(varchar(20), --converting the following date to varchar of length 20 maximum
getdate(), 112) + '_' + -- getdate() is current time on server - convert to 112 = yyyymmdd
replace(convert(varchar(20), getdate(), 114), ':', '') --replace the : with '' empty space - converted to 114 = hh:mi:ss:mmm (24h)
Good explanation on w3schools https://www.w3schools.com/sql/func_convert.asp
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
May 25, 2017 at 3:28 pm
Henrico Bekker - Thursday, May 25, 2017 2:10 PMQuestion, why aren't you using the perfectly good SQL Maintenance plans to perform all of this?
I am using the maintenance plan but learning advanced tsql and automate task without using GUI, that's my long term goal. I would like to gain that confidence for me because eventually, I would like to make myself valuable.
May 25, 2017 at 3:32 pm
newdba2017 - Thursday, May 25, 2017 3:28 PMHenrico Bekker - Thursday, May 25, 2017 2:10 PMQuestion, why aren't you using the perfectly good SQL Maintenance plans to perform all of this?I am using the maintenance plan but learning advanced tsql and automate task without using GUI, that's my long term goal. I would like to gain that confidence for me because eventually, I would like to make myself valuable.
Good enough reason!
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
May 26, 2017 at 5:38 pm
Henrico Bekker - Thursday, May 25, 2017 3:32 PMnewdba2017 - Thursday, May 25, 2017 3:28 PMHenrico Bekker - Thursday, May 25, 2017 2:10 PMQuestion, why aren't you using the perfectly good SQL Maintenance plans to perform all of this?I am using the maintenance plan but learning advanced tsql and automate task without using GUI, that's my long term goal. I would like to gain that confidence for me because eventually, I would like to make myself valuable.
Good enough reason!
Heh - That's the best reason. 😀
You'll want to start in the msdb database by looking at the dbo.backupset and dbo.backupmediafamily.
backupset: https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/backupset-transact-sql
backupmediafamily: https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/backupmediafamily-transact-sql
Join them together like this to get a list of full backups for a single database or remove the WHERE clause predicate to restrict the database name to query them all.
SELECT bs.database_name, bs.type, bs.backup_finish_date, bmf.physical_device_name
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE bs.type = 'D'
AND bs.database_name = 'Your Database Name'
ORDER BY bs.backup_finish_date DESC;
Before you start down this road, take a look at what your company's strategy is for backups. You should have log backups firing at a specific interval and full backups running less frequently. You could have differential backups running in there too. Find out when they're taken, where they're written and also how long you keep them. Take a look at the whole picture and understand what it means for your logs, any recovery that becomes necessary and disk space - for the data drive, log drive and backup drive.
If the backups are on the same machine as the data and log files, make a change as soon as you can so they aren't on the same machine. Immediately would not be too soon. If the server gets caught in a building fire, takes a heavy electrical hit or otherwise dies and the drives are lost, you'll lose everything. The first duty of a DBA is to protect the data, so make sure it's protected. Learning how this stuff works is a good goal and it will certainly never hurt you to know it inside and out.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply