January 24, 2011 at 6:36 am
Hi,
We currently use SQL Compare V8.50.12.20 to create snapshots of database before rolling out any upgrades or patches. Some of our servers have a lot of databases on and we need to take a snapshot of all of them before performing some service pack upgrades. At the moment SQL Compare seems a bit limited in its snapshot functionality in that you can only snapshot one database at a time.
Is there a way of writing some sort of script that will snapshot every DB on a given server? Is there another tool we could use to perform this job that offers the same capability as SQL Compare with regards to rollout and rollback scripting?
Thanks in advance for any ideas.
Simon
January 24, 2011 at 1:25 pm
Seems like there is a command line option for SQL Compare that might work. Couldn't dig too far into it - kept running into 404 errors.
_____________________________________________________________________
- Nate
January 25, 2011 at 2:06 am
Nice one thanks. I'm getting some 404 errors too. Hopefully i'll be able to hack enough info out to get started though.
Cheers,
Simon
January 25, 2011 at 3:49 am
Are these physical or virtual SQL servers? If they are virtual could you not just take a snapshot of the entire server?
January 25, 2011 at 3:50 am
Hi,
They're physical servers so that's not possible unfortunately.
Thanks,
Simon
January 25, 2011 at 3:52 am
Oh well nevermind forget I said anything 🙂
January 25, 2011 at 6:19 am
Just an FYI: I contacted Red Gate about the 404s and they referred me to documentation on a different release.
_____________________________________________________________________
- Nate
January 25, 2011 at 6:36 am
Yes, you should be able to do this from the command line, I've done it before. I'll add this to a list of new articles I'm working on (just started working for Red Gate) and I'll get back to you guys as soon as I can. It might take a little while, I'm still in my two-weeks notice time at the old job.
"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
January 25, 2011 at 7:21 am
Nice one thanks Grant.
I've just started playing with it now. I can get it to generate the snapshots i want but i'm trying to find out if i can write the script in a way that fetches all the DBs for me rather than having to specify them manually.
Oh well, i'll keep tinkering but look forward to your articles on it in the future.
January 25, 2011 at 8:46 am
If it will help your tinkering (and you can write an article if you get done first), the approach I was going to take was to use PowerShell to do connect to the server & loop through the user databases making the command line call to SQL Compare.
"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
January 26, 2011 at 1:06 pm
Grant Fritchey (1/25/2011)
Yes, you should be able to do this from the command line, I've done it before. I'll add this to a list of new articles I'm working on (just started working for Red Gate) and I'll get back to you guys as soon as I can. It might take a little while, I'm still in my two-weeks notice time at the old job.
Now THAT's interesting! Any idea WHEN and WHERE that article will be published? I'd like to drop it on our System DBA's and a couple of "decision makers" (read that as CTO, CIO, and a couple of other "C" level managers". And, YES, we have the need.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 26, 2011 at 4:48 pm
Jeff Moden (1/26/2011)
Grant Fritchey (1/25/2011)
Yes, you should be able to do this from the command line, I've done it before. I'll add this to a list of new articles I'm working on (just started working for Red Gate) and I'll get back to you guys as soon as I can. It might take a little while, I'm still in my two-weeks notice time at the old job.Now THAT's interesting! Any idea WHEN and WHERE that article will be published? I'd like to drop it on our System DBA's and a couple of "decision makers" (read that as CTO, CIO, and a couple of other "C" level managers". And, YES, we have the need.
As soon as I can. Promise. I work for the old employer for one more week (last day is Thursday, next week). When I'm not doing nine hours a day + 1.5 hours commuting, I'm going to start churning out articles. This will be one of the first. Publication will probably be somewhere on the Red Gate site, not sure where yet (we're still working out some of the details). It'll happen soon. If I wasn't leading a winter survival campout this weekend, it'd happen sooner.
"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
January 26, 2011 at 11:52 pm
Hi,
In the end i did manage to get this to run through the command line by using the following:
sqlcompare /server1:xx.xx.xx.xx/database1:master /username1:%username% /password1:%password% /makesnapshot:"%folderpath%\master.snp"
I've got this running in a batch file, it's an ugly solution but for now it does what we want. I'd like to have some time later to come back and review it and go down the powershell route so that the solution can be rolled out more universally but for now that will have to wait...
Thanks all for your help.
Thanks,
Simon
January 27, 2011 at 7:02 pm
This needs a ton of work to make it generic, parameterized, but if you guys want the raw form, this powershell script will automatically generate scripts for all databases on a server:
$databases = Invoke-Sqlcmd -Query "SELECT d.name FROM sys.databases AS d" -ServerInstance "grant-red1\gfr1"
$databases | ForEach-Object {Invoke-Expression "sqlcompare.exe /s1:""grant-red1\gfr1"" /db1:$($_.name) /mkscr:c:\dbscripts\$($_.name) " }
The first command loads all databases (first place to clean up, I need to make it only load user databases) into a variable (leading to the second place, I think I can pipe this whole thing instead of looping). With the variable, we do a forreach-object to simply pull out the databases. I run the sqlcompare against a particular location & server, but I do use the database name to generate the folder. All this can be parameterized (and should be). I'll be working on making it neater, but this will work.
Oh, and one other thing, paths were making me nuts when I was trying to work with, so I set my SQLCompare directory into my system Path statement so I could just use the command. To simply use the script above, you'd need to do the same.
I hope this is helpful. I'll be putting together a much cleaner version of this for publication (not sure where yet, we haven't determined where I publish to at Red Gate).
"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
January 28, 2011 at 6:11 am
Did you try new features (Schema / Data compate) available in Visual studio 2010 ?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply