CODEWITHSUNDEEP
sql-server
JonnyRaa
JonnyRaa

Reputation: 8038

Copying databases in sql server

I've been busy looking at various ways to copy databases in sqlserver and seem to be totally stuck! There are various questions and answers on the topic but none of them seem to fit my scenario.

Basically what I'm trying to do is set up a database in a particular state (schema + data) preferably by any method I choose - mucking around in an application, writing inserts etc. Then I'd like to preserve a copy of that database which can be used as the state that a particular set of tests run against/require. I need to be able to drop this into any database name and not just the one that it happened to be when I extracted it. I need this to work from the command line for the build server.

Ideally I'd like to be able to do something like this:

outputFile = "something.IDontCareWhatExtension"
databaseName = "aDatabase"
SaveDatabase(databaseName, outputFile)

and somewhere else:

inputFile = "theFilename"
databaseName = "somethingOtherDatabase"
Load(databaseName, inputFile);

It doesnt seem like a big ask! I've been doing something similar in oracle with imp + exp and it basically does what I want.

So far I've tried using the SMO .net libraries. This is what's used under the hood in SSMS when you do a database right click => tasks => generate scripts. These basically don't work as you'd expect and require a surprising amount of programming effort to get things like constraints out. Before sinking more time into this approach I thought I'd look in more detail at backup and restore but these appear to only be geared up for backing up and restoring things(maybe the clue was in the name!)! Eg they dont seem to let you say what database to install the backup to.

Help please! Surely it can't be this hard to do?!

The only other thing I can think is that I should just take a different approach and write database installation scripts instead - where I generate the schema for a particular version and then hand craft a load of insert statements. That would stop us pulling client data over from a server to reproduce bugs though.

Upvotes: 0

Views: 320

Answers (1)

Dave.Gugg
Dave.Gugg

Reputation: 6781

You could backup the database when you get it to the particular state you want, then restore that backup for your tests.

BACKUP DATABASE [ReadyForTesting] TO  DISK = N'C:\Temp\Backup.bak' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'ReadyForTesting-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Then for restoring, just specify a different name to make it a copy, or clone:

USE [master]
RESTORE DATABASE [Restored_Copy] FROM  DISK = N'C:\Temp\Backup.bak' WITH  FILE = 1,  MOVE N'dbfile' TO N'C:\MDFPath\Filename.mdf',  MOVE N'LogFile' TO N'C:\LogPath\Filename_log.LDF',  NOUNLOAD,  STATS = 5

GO

Upvotes: 1

Related Questions