August 9, 2017 at 9:21 am
EXECUTE sp_msforeachdb 'USE [?]IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'')BACKUP DATABASE [?] TO DISK = ''C:\MyBaks\?.bak'' WITH INIT, COPY_ONLY, COMPRESSION'I need to append date time in this script for ex userdb_08082017.bak. How to achieve this?
August 9, 2017 at 9:32 am
Create a variable with the date value and pass that in your statement. For example:DECLARE @CurrDate varchar(8) = REPLACE(CONVERT(varchar(10), GETDATE(), 104),'.','');
...
... DISK = ''C:\MyBaks\?_' + @CurrDate + '.bak'' ...
That should give you enough to fill in the blanks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 9, 2017 at 10:19 am
Instead of rolling your own scripts, why not look at some of the free scripts out there that do this work like Minion Backup. They'll be much more efficient.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 10, 2017 at 5:29 am
Thom A - Wednesday, August 9, 2017 9:32 AMCreate a variable with the date value and pass that in your statement. For example:DECLARE @CurrDate varchar(8) = REPLACE(CONVERT(varchar(10), GETDATE(), 104),'.','');
...
...DISK = ''C:\MyBaks\?_' + @CurrDate + '.bak''...
That should give you enough to fill in the blanks.
i am getting an error next to the + symbol
August 10, 2017 at 5:35 am
premkuttan - Thursday, August 10, 2017 5:29 AMThom A - Wednesday, August 9, 2017 9:32 AMCreate a variable with the date value and pass that in your statement. For example:DECLARE @CurrDate varchar(8) = REPLACE(CONVERT(varchar(10), GETDATE(), 104),'.','');
...
...DISK = ''C:\MyBaks\?_' + @CurrDate + '.bak''...
That should give you enough to fill in the blanks.i am getting an error next to the + symbol
The error would be nice. 🙂 Also, however, have you tried Grant's advice?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 10, 2017 at 5:53 am
Thom A - Thursday, August 10, 2017 5:35 AMpremkuttan - Thursday, August 10, 2017 5:29 AMThom A - Wednesday, August 9, 2017 9:32 AMCreate a variable with the date value and pass that in your statement. For example:DECLARE @CurrDate varchar(8) = REPLACE(CONVERT(varchar(10), GETDATE(), 104),'.','');
...
...DISK = ''C:\MyBaks\?_' + @CurrDate + '.bak''...
That should give you enough to fill in the blanks.i am getting an error next to the + symbol
The error would be nice. 🙂 Also, however, have you tried Grant's advice?
Ahh, yes, I see my faux pas. Try:DECLARE @CurrDate varchar(8) = REPLACE(CONVERT(varchar(10), GETDATE(), 104),'.','');
DECLARE @SQL varchar(max) = 'USE [?]
IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'')
BACKUP DATABASE [?] TO DISK = ''C:\MyBaks\?_' + @CurrDate + '.bak'' WITH INIT, COPY_ONLY, COMPRESSION';
EXECUTE sp_msforeachdb @SQL;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 10, 2017 at 9:44 am
This script works good, but in my environment I have 100 dbs so I need to take first 50
DB in one path and second set in another path. My plan is to do this via DB id. Can you write a script in that way
August 10, 2017 at 10:05 am
premkuttan - Thursday, August 10, 2017 9:44 AMThis script works good, but in my environment I have 100 dbs so I need to take first 50
DB in one path and second set in another path. My plan is to do this via DB id. Can you write a script in that way
Yes, but i still suggest you look at Grant's suggestion first before I provide a solution.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 10, 2017 at 12:38 pm
Grant Fritchey - Wednesday, August 9, 2017 10:19 AMInstead of rolling your own scripts, why not look at some of the free scripts out there that do this work like Minion Backup. They'll be much more efficient.
I have to ask... how will they be "more efficient"?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 10, 2017 at 12:43 pm
premkuttan - Thursday, August 10, 2017 9:44 AMThis script works good, but in my environment I have 100 dbs so I need to take first 50
DB in one path and second set in another path. My plan is to do this via DB id. Can you write a script in that way
What will you use to determine what the "first 50" actually are and what are the requirements for when you add a new database? For example, if the addition of a new database bumps one of the "first 50" out of the "first 50", should it continue to backup to the path previous used for the bumped database or allow itself to be "bumped" to the "second 50". The same question arises for when you drop a database and it leaves a "hole" in the first 50.
Last but not least, why are you backing the same server up to more than 1 main path?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 10, 2017 at 1:30 pm
Jeff Moden - Thursday, August 10, 2017 12:43 PMWhat will you use to determine what the "first 50" actually are and what are the requirements for when you add a new database? For example, if the addition of a new database bumps one of the "first 50" out of the "first 50", should it continue to backup to the path previous used for the bumped database or allow itself to be "bumped" to the "second 50". The same question arises for when you drop a database and it leaves a "hole" in the first 50.
Last but not least, why are you backing the same server up to more than 1 main path?
The biggest question I have is Jeff's last one. Why backup to more than one root directory? If it's because of space, then you need to get drives (or SAN volumes, etc.) to support the backups you need to keep.
If you're backing up to physical drives on the same server, DON'T DO IT. Having the backups on the same hardware as the databases creates a single point of failure that isn't that hard to hit. What if the server is stolen? What if there's a fire in that part of the building? Do you ever have electrical problems in the building? The disaster scenarios are nearly endless and your recovery plan has to account for them...at least some of them.
August 10, 2017 at 2:18 pm
Jeff Moden - Thursday, August 10, 2017 12:38 PMGrant Fritchey - Wednesday, August 9, 2017 10:19 AMInstead of rolling your own scripts, why not look at some of the free scripts out there that do this work like Minion Backup. They'll be much more efficient.I have to ask... how will they be "more efficient"?
Maybe not more efficient, but certainly I am reinventing the wheel here. Ola Hallengren's scripts, for example, are very good for a lot of tasks and easily customised.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 10, 2017 at 3:37 pm
Jeff Moden - Thursday, August 10, 2017 12:38 PMGrant Fritchey - Wednesday, August 9, 2017 10:19 AMInstead of rolling your own scripts, why not look at some of the free scripts out there that do this work like Minion Backup. They'll be much more efficient.I have to ask... how will they be "more efficient"?
More efficient to use well-established and tested scripts rather than write your own as an experiment for the first time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply