January 17, 2012 at 3:50 am
Hey guys,
I've just been thinking about a few scenarios and it's struck me that I don't have much of a rollback plan as regards SSRS reports or their associated objects. If I outline the way I work perhaps you could share what you use to achieve this?
1. All my reports are created via Stored Procedures and within these typically a few functions specific to the report in question (but not always)
2. Reports are created via the Wizard then edited all in Visual Studio 2008
3. Reports are then deployed to a server about 50 foot away.
The objects I create are on a 'live' database, i.e. I don't develop them on one database and move them to another, they're just straight on the db where the data is.
I am the only person who creates these reports and the associated objects are not shared with others, and in most cases (functions being the exception), used exclusively for reports.
I guess what I'm after is something which would allow similar functionality to Shadow Copies within Windows, i.e. I could right click on an SP and restore to a previous time point, either over the top of the existing SP or (the preferred approach) to a new SP. The same goes for report files too I guess. Thinking about this I'm not sure if Shadow Copies could be the solution for the report files - we take them every 2 hours and the projects are stored on a server.
I think the fact that I'm operating alone (but using a database others use), and not developing on a 'test' database first then moving to a live one, perhaps changes things compared to the typical setup here. (I should add that I do have access to test databases and from time to time do use these)
Any suggestions much appreciated 🙂
January 17, 2012 at 4:22 am
Do you have source control in place for other code?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 17, 2012 at 4:29 am
The devs that use the same live database do, but I do not. I believe they each have their own databases then deploy to the live database (the one which I'm using).
Not sure how this would work for me though since I don't work on a local version and deploy to a live version.
January 17, 2012 at 4:43 am
You can create a management studio database project and associate this with a sourcecontrol platform such as Subversion (free), Team Foundation Server (expensive) - depends on your full requirements.
You can also create your database projects using Visual Studio and then bind these to your source control repository.
Personally I prefer to use Visual Studio rather than SSMS for database script projects.
If your going to use Subversion the version we use and have not had any problems with and is very easy to configure is: http://www.visualsvn.com/visualsvn/download/
You will need to also download a couple of other tools like TortoiseSVN and ankhsvn to allow our project to integrate with the repository.
Hope this helps.
Rob
January 17, 2012 at 4:49 am
Thanks for the reply Rob. Subversion and Tortoise SVN are two names I recognise from the devs speaking about it so there's a good chance they're using them which is a good starting point!
Moving off topic slightly, I'm intrigued by your comment about Visual Studio though. I use SSMS to create the stored procedures, preview the data etc. then once happy I fire up VS, open the project I'm working on and either create a new report or edit an existing one.
Perhaps because I'm used to SSMS and have used it for years, but I don't find VS as stable and user friendly. How do you go about using VS instead of SSMS and why do you prefer it?! Is it just personal choice or is this the 'typical' way of working when creating reports based on SPs etc?
Thanks,
Rob
January 17, 2012 at 4:54 am
Here you go...
http://www.red-gate.com/products/sql-development/sql-source-control/
Aside. Developing directly on the production server is one hell of a risk. Sooner or later a miss-type or other similar happening is going to cause major problems (been there, done that, got the overtime). If you have access to a dev or test server, why aren't you developing on the development or test server
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 17, 2012 at 4:59 am
Check this out for using Visual Studio 2008 for database projects (I take it your not using 2010, as they made a few improvements there):
I guess the main reason I use VS to keep all my scripts in is you can combine your database project with report server project in one solution and just check the whole lot in in one batch.
Rob
January 17, 2012 at 4:59 am
GilaMonster (1/17/2012)
Here you go...http://www.red-gate.com/products/sql-development/sql-source-control/
Aside. Developing directly on the production server is one hell of a risk. Sooner or later a miss-type or other similar happening is going to cause major problems (been there, done that, got the overtime). If you have access to a dev or test server, why aren't you developing on the development or test server
I guess the ideal scenario would be a mirrored version of the production server and all it's data. The devs work on databases which might be weeks or even months out of date in terms of the actual data stored in the database. For the sort of work I'm doing the dev or test database would have to be restored at least daily. We've spoken about such things in the past but nothing has materialised. It's not that I'm a risk lover, just not much of an option or a process for how I work is in place at the moment. Something I'm keen to address!
January 17, 2012 at 5:10 am
Robert Murphy UK1 (1/17/2012)
Check this out for using Visual Studio 2008 for database projects (I take it your not using 2010, as they made a few improvements there):I guess the main reason I use VS to keep all my scripts in is you can combine your database project with report server project in one solution and just check the whole lot in in one batch.
Rob
Thanks for the link Rob. I've given it a brief look but it's something I'll read with more detail a little later on - it certainly looks quite interesting. The idea of everything being self contained is quite attractive I guess.
Sadly we only have 2008 VS.
January 19, 2012 at 7:17 am
you can also try this tool:
http://nobhillsoft.com/randolph.aspx
does what you're looking for, with zero effort on your part
January 19, 2012 at 7:40 am
Basically going to go down the dev route here, SQL Source Control from Redgate is what they use, think I'll give the same thing a whirl.
January 19, 2012 at 7:54 am
Cool. It should work well for you.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 19, 2012 at 7:56 am
Hopefully, just trying to suss out how I can automate a database restore to a test database, looks like RESTORE DATABASE xxx FROM C:\.... would do it, wrapped into a job and scheduled, sure it's not quite that simple though!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply