Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Automate Your Backup and Restore Tasks Expand / Collapse
Author
Message
Posted Monday, October 4, 2010 9:15 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 5:29 AM
Points: 621, Visits: 1,073
Comments posted to this topic are about the item Automate Your Backup and Restore Tasks

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper
Post #998046
Posted Tuesday, October 5, 2010 3:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 2:10 AM
Points: 1, Visits: 89
Hi,


To drop a database (in this case: Develop) use this script:

if exists (select name from master.sys.databases where name = 'Develop')
begin
alter database Develop
set restricted_user with rollback immediate

drop database Develop
end

Works all the time.
Post #998169
Posted Tuesday, October 5, 2010 4:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 2, 2012 4:50 AM
Points: 10, Visits: 23
Hello,
I do the same in my production system with the following, relatively simple 4 steps job:

Step1: Alter dev database to Single user mode to prepare it for the restore. (No need to delete the database or the datafiles first.)

ALTER DATABASE DevSystem SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

Step2 Restoring Production database from the production server using a network path, containind the backup file.
(I use the MOVE command for the data and log files due to different storage paths. )

RESTORE DATABASE [DevSystem] FROM DISK = N'\\ProductionServer\BackupFolder\Dev.bak' WITH FILE = 1,
MOVE N'Datafile1' TO N'd:\MSSQL\Data\DevSystem_data1.mdf',
MOVE N'Datafile2' TO N'd:\MSSQL\Data\DevSystem_data2.ndf',
MOVE N'Logfile' TO N'd:\MSSQL\Data\DevSystem_log.LDF', NOUNLOAD, REPLACE, STATS = 90
GO

Step 3: Set the database to working state..

ALTER DATABASE DevSystem SET MULTI_USER
go

Step 4: Finally, if the dev database is on a different SQL Servel like in my case is essential to map the login username of the 1st SQL to login username of the 2nd with the following command:

sp_change_users_login update_one ,exampleusername,exampleusername
go

Regards

Nikos
Post #998193
Posted Tuesday, October 5, 2010 7:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 80, Visits: 714
"I started as I do with most things by over complicating the solution! "

LOL, I thought I was the only person that did that.

I usually refer to it as making 3 left-turns to go right...but at least I end up in the right position.
Post #998333
Posted Tuesday, October 5, 2010 7:29 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 11:25 AM
Points: 70, Visits: 604
That's a good one Nikos, Thanks for sharing your experience.

nikosag (10/5/2010)
Hello,
I do the same in my production system with the following, relatively simple 4 steps job:

Step1: Alter dev database to Single user mode to prepare it for the restore. (No need to delete the database or the datafiles first.)

ALTER DATABASE DevSystem SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

Step2 Restoring Production database from the production server using a network path, containind the backup file.
(I use the MOVE command for the data and log files due to different storage paths. )

RESTORE DATABASE [DevSystem] FROM DISK = N'\\ProductionServer\BackupFolder\Dev.bak' WITH FILE = 1,
MOVE N'Datafile1' TO N'd:\MSSQL\Data\DevSystem_data1.mdf',
MOVE N'Datafile2' TO N'd:\MSSQL\Data\DevSystem_data2.ndf',
MOVE N'Logfile' TO N'd:\MSSQL\Data\DevSystem_log.LDF', NOUNLOAD, REPLACE, STATS = 90
GO

Step 3: Set the database to working state..

ALTER DATABASE DevSystem SET MULTI_USER
go

Step 4: Finally, if the dev database is on a different SQL Servel like in my case is essential to map the login username of the 1st SQL to login username of the 2nd with the following command:

sp_change_users_login update_one ,exampleusername,exampleusername
go

Regards

Nikos
Post #998341
Posted Tuesday, October 5, 2010 7:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 9:00 AM
Points: 2, Visits: 15
Nikos, I do pretty much the same thing that you do. However, could you explain Step #4 a bit more. The one thing I generally have trouble with is the logins and haven't gotten a satisfactory solution yet. What does this step do?


Step 4: Finally, if the dev database is on a different SQL Servel like in my case is essential to map the login username of the 1st SQL to login username of the 2nd with the following command:

sp_change_users_login update_one ,exampleusername,exampleusername
go

Regards

Nikos
Post #998361
Posted Tuesday, October 5, 2010 8:20 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
Why do this task with VB script when you can do the whole process with T-SQL scripts?

SQL DBA.
Post #998388
Posted Tuesday, October 5, 2010 8:47 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 5:29 AM
Points: 621, Visits: 1,073
Hi.

Why use VB scripts.


I said not everyone would agree with my approach! :)

The VB scripts supported what I wanted to achieve.

Kind Regards,
Phil.


-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper
Post #998426
Posted Tuesday, October 5, 2010 8:59 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 6:20 PM
Points: 33,078, Visits: 15,192
SanjayAttray (10/5/2010)
Why do this task with VB script when you can do the whole process with T-SQL scripts?


Do you somehow think that T-SQL is "better"

I think either approach can work well. I like VB for file work as it appears cleaner to me.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #998447
Posted Tuesday, October 5, 2010 10:10 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:13 PM
Points: 21,229, Visits: 14,937
Nice article and an important topic. Thanks for sharing.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #998538
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse