September 17, 2015 at 12:56 am
Hi together,
i have a problem with the if exists statement...the goal is only run stuff if the database is online on the current System so i did this
IF EXISTS (SELECT name FROM master.sys.databases
WHERE name = N'Database' AND state_desc = 'ONLINE')
BACKUP DATABASE [Database] TO DISK = N'\\UNC-Path\Database.bak'
WITH NOFORMAT, INIT, NAME = N'Database-Vollständig Datenbank Sichern', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
BACKUP LOG [Database] TO DISK = N'\\UNC-Path\Database-log.trn'
WITH NOFORMAT, INIT, NAME = N'Database-Transaktionsprotokoll Sichern', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
BACKUP LOG runs everytime...so i did
IF EXISTS (SELECT name FROM master.sys.databases
WHERE name = N'Database' AND state_desc = 'ONLINE')
BACKUP DATABASE [Database] TO DISK = N'\\UNC-Path\Database.bak'
WITH NOFORMAT, INIT, NAME = N'Database-Vollständig Datenbank Sichern', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
IF EXISTS (SELECT name FROM master.sys.databases
WHERE name = N'Database' AND state_desc = 'ONLINE')
BACKUP LOG [Database] TO DISK = N'\\UNC-Path\Database-log.trn'
WITH NOFORMAT, INIT, NAME = N'Database-Transaktionsprotokoll Sichern', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
now BACKUP DATABASE and LOG runs only when the database is online...but if i want to do now stuff like this
IF EXISTS (SELECT name FROM master.sys.databases
WHERE name = N'Database' AND state_desc = 'ONLINE')
BACKUP DATABASE [Database] TO DISK = N'\\UNC-Path\Database.bak'
WITH NOFORMAT, INIT, NAME = N'Database-Vollständig Datenbank Sichern', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
IF EXISTS (SELECT name FROM master.sys.databases
WHERE name = N'Database' AND state_desc = 'ONLINE')
BACKUP LOG [Database] TO DISK = N'\\UNC-Path\Database-log.trn'
WITH NOFORMAT, INIT, NAME = N'Database-Transaktionsprotokoll Sichern', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
IF EXISTS (SELECT name FROM master.sys.databases
WHERE name = N'MailM' AND state_desc = 'ONLINE')
USE Database
GO
USE Database runs everytime....regardless what i do.
How can i fix this?
Thanks in advance
September 17, 2015 at 2:02 am
Wurstsalat (9/17/2015)
BACKUP LOG runs everytime
Add BEGIN ... END?
IF EXISTS
BEGIN
BACKUP DATABASE [Database] ...
BACKUP LOG [Database] ...
END
Don't think you can use "USE" to change databases mid-batch like that, I think you would have to use dynamic SQL
September 17, 2015 at 3:33 am
Yep, thanks
BEGIN/END fixed the first flaw....but not the "USE" flaw for dynamic SQL i have to read much i think...saw a few articles about it ( i am not very common in programming SQL )
I will check this out until no one else has another great idea about this...
September 17, 2015 at 5:00 am
Wurstsalat (9/17/2015)
Yep, thanksBEGIN/END fixed the first flaw....but not the "USE" flaw for dynamic SQL i have to read much i think...saw a few articles about it ( i am not very common in programming SQL )
I will check this out until no one else has another great idea about this...
Why do you want a use database statement, database focus is irrelevant for backup commands.
Your default database will be sufficient
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 17, 2015 at 5:22 am
i would like to do a index reorg after or before backup and in general i like the option to run commands on the machine which holds the database online (no depencies)
also i would like to do a shrink after reorg
edit
hm i could do this in a seperate SQL Agent Job and Trigger it after/before backup? hmmmm more spreaded Jobs but....should work? i think/hope
anyway, still thankfull for any other solution/idea
September 17, 2015 at 5:46 am
Don't know if it would help? but you can be connected to the database when you do the Backup (but not a Restore)
So, in principle, you could do
USE MyDatabase
BACKUP MyDatabase
Rebuild Indexes in MyDatabase
I don't suppose it will solve ALL of the maintenance tasks you might like to do, but you can also use 3-part naming to Rebuild an index - so you can be connected to a different database and use
ALTER INDEX MyIndexName
ON MyDatabase.dbo.MyTable
REBUILD or REORGANISE
September 17, 2015 at 5:48 am
Wurstsalat (9/17/2015)
i could do this in a seperate SQL Agent Job and Trigger it after/before backup?
Rather than separate jobs how about a SQL Agent Job with multiple steps?
Step 1 : Backup
Step 2 : Rebuild indexes etc.
You can set the "action" to either abort, or continue with the next step, or continue with Step N, if an error occurs during one of the steps.
September 17, 2015 at 6:03 am
i liked this idea but i think i have to do one backup job for each database and i dont like to do to much "parallel" backups but still want to do all backups in the shortest possible timeframe (-> best with no "dead times" while backup time).
So i have a plan/timeline which i should monitor over the time
Hm still not my favorite but i like your thinking
September 17, 2015 at 7:02 am
Wurstsalat (9/17/2015)
also i would like to do a shrink after reorg
STOP shrinking your databases!!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 17, 2015 at 7:10 am
Wurstsalat (9/17/2015)
i think i have to do one backup job for each database and i dont like to do to much "parallel" backups but still want to do all backups in the shortest possible timeframe
One SQL Agent job, One step PER database backup?
We use a procedure which backups up all databases, one-after-the-other, except for any database names in an "exclusion list". So if we create a new database it is automatically added to the backup process (until/unless we put the name of the database in the Exclusion list)
September 17, 2015 at 7:24 am
Perry Whittle (9/17/2015)
Wurstsalat (9/17/2015)
also i would like to do a shrink after reorgSTOP shrinking your databases!!
the space is only needed for the reorgs, not while running productive (no auto expanding is needed while production, if this would happen we would set a bigger size).
We have a few big tables which bloats the databases to 1 3/4 of "normal" size, if we do nothing we would run out of disk space and the reorgs wouldnt be possible... (yes someone could buy more disks but the guy with the Money said no)
So we have to decide, reorg or shrink to a size which does not effect the production but the reorgs...would you consider not to shrink/reorg?
September 17, 2015 at 7:55 am
i would like to use a procedure which backups all databases except those in a exclusion list but i dont know how to do this + onlinecheck (like i wrote, i am not very comon in writing SQL)...if i dont do this check i have always errors in the Job
September 17, 2015 at 8:42 am
Wurstsalat (9/17/2015)
but i dont know how to do this
Have a look at Minion Backup perhaps? or the Backup from Ola Hallengren? Both also do Index Rebuild etc.
September 18, 2015 at 3:45 am
Wurstsalat (9/17/2015)
Perry Whittle (9/17/2015)
Wurstsalat (9/17/2015)
also i would like to do a shrink after reorgSTOP shrinking your databases!!
the space is only needed for the reorgs, not while running productive (no auto expanding is needed while production, if this would happen we would set a bigger size).
We have a few big tables which bloats the databases to 1 3/4 of "normal" size, if we do nothing we would run out of disk space and the reorgs wouldnt be possible... (yes someone could buy more disks but the guy with the Money said no)
So we have to decide, reorg or shrink to a size which does not effect the production but the reorgs...would you consider not to shrink/reorg?
when you shrink you'll likely undo all the rebuild or reorg work anyway as pages may get moved during the shrink operation, so your efforts in the first place are futile.
Disks are cheap nowadays, there's no excuse!!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 18, 2015 at 8:57 am
Perry Whittle (9/18/2015)
when you shrink you'll likely undo all the rebuild or reorg work anyway as pages may get moved during the shrink operation, so your efforts in the first place are futile.
+1 (well, + lots!)
Kristen's suggestion of Ola Hallengren's scripts is a good shout too. Takes care of all the dynamic SQL for you & logs work done.
Reorgs should only take log space, not additional space in the database data files.
Rebuilds do take extra space, but should only do it an index at a time (and therefore only use extra space equivalent to the size of that index)
- make sure you're only doing indexes that need it and not using REBUILD ALL.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply