March 27, 2012 at 5:06 pm
Hello Folks;
I spend several hours browsing and I can't find the answer for my issue, I'll explain the goal and so you guys can give suggestions
I need to create an script that restore all databases from SQLproduction to N instance (maybe local)
so I created almost all of the script.
-I'm in the instance for SQLproduction
-get the database list
-backup the DBs in a local folder.
-now I need to restore in my (local) instance
is it possible to do something like
RESTORE DATABASE [Sales] FROM DISK = N'c:\sales.bak' WITH FILE = 1, NOUNLOAD, STATS = 10 -instance (local)
or change from SQLproduction to the local instance using a command similar to
sp_dropserver <old_name\instancename>
My SQL version: Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Thank you in advance
March 27, 2012 at 6:26 pm
When you say you're "in the instance for SQLproduction" do you mean you have an open window in SSMS? Or are you using some other means of backing up the databases?
The RESTORE command you offered is a T-SQL command, and does not accept an instance name. That's not how T-SQL works. You need to be inside a T-SQL session, e.g. in a query window in SSMS, to execute the RESTORE DATABASE command.
Steps:
1. back up all the databases from production to folder - you say this is done
2. stage the backup files in a place your local SQL Server can reach, i.e. on your local C:\ drive
3. connect to your local SQL Server instance and issue the proper RESTORE DATABSE commands, pointed to the staged files
Note that in step 3, just as in the BACKUP DATABASE command in step 1, the path to the backup file is relative to the SQL instance.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 28, 2012 at 7:23 am
Thanks for your replay, the whole idea is to refresh all the databases from the production server but locally, so any developer can run the script and refresh their (local) copies and also I can schedule a job from the DEV server and refresh the databases every weekend.
So when I'm connected in Management Studio to the server insatance call SQLproduction and run my script, I'm able to donwload all the databases in a folder in my machine, now I want to be able to restore them in my (local) instance without opening another query window to my (local) instance and runing the restore statement.
Now I know the Restore command does not accept "instance name" as an option, that's why I was hoping that is posible to disconnect and then reconnect to my (local) instance and then run the restore command.
I realized I can do this in SSIS but this is not helpful because then everytime a DEV needs to update their database I will have to modify or have an SSIS package for each DEV, I rather have a simple store proc that I can execute and it will retore the databases on the machine I execute it from.
March 28, 2012 at 9:47 am
jokesbox_mail (3/28/2012)
Thanks for your replay, the whole idea is to refresh all the databases from the production server but locally, so any developer can run the script and refresh their (local) copies and also I can schedule a job from the DEV server and refresh the databases every weekend.So when I'm connected in Management Studio to the server insatance call SQLproduction and run my script, I'm able to donwload all the databases in a folder in my machine, now I want to be able to restore them in my (local) instance without opening another query window to my (local) instance and runing the restore statement.
Now I know the Restore command does not accept "instance name" as an option, that's why I was hoping that is posible to disconnect and then reconnect to my (local) instance and then run the restore command.
I realized I can do this in SSIS but this is not helpful because then everytime a DEV needs to update their database I will have to modify or have an SSIS package for each DEV, I rather have a simple store proc that I can execute and it will retore the databases on the machine I execute it from.
Question for you, is it important that each time a developer wants to "refresh" their local databases, that new backups be taken from production?
That seems risky, as it means developers could potentially interfere with the production backup strategy.
I encourage developers to refresh non-production databases from production backups as needed, and enable them to do so by providing copies of production backups, but I do not allow them to take new backups from production.
Why not simply provide each developer a script that can "refresh" all local databases from a network share containing copies of the production backups taken from the night before? This way you do not need to worry about them connecting to production first, and then having to disconnect and reconnect to their local instance, and they will not impact production servers or backups.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply