0

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.

JonnyRaa
  • 7,559
  • 6
  • 45
  • 49
  • hm so the problem is that your restored database always has the same name of the one you backed up? wouldn't a simple renaming after the restoring do it? – DrCopyPaste May 22 '14 at 15:31
  • You could write a stored procedure to backup and restore your db. Then call it from a console app. Have you looked into that? – Rick S May 22 '14 at 15:32
  • @DrCopyPaste could you elaborate? – JonnyRaa May 22 '14 at 15:33

1 Answers1

1

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
Dave.Gugg
  • 6,561
  • 3
  • 24
  • 43
  • Are you just looking for the syntax for running a database backup and restore? – Dave.Gugg May 22 '14 at 15:37
  • what are the values that need to go into Move N'dbfile'? I'm getting some syntax errors. I thought these were constants at first! – JonnyRaa May 22 '14 at 16:08
  • 1
    The first is the logical name and the second is the path and filename. You will need one move for the mdf, one for the ldf, and then extra ones for any ndf files. You can find this info by right clicking on the database in SSMS and going to properties, then looking at the Files tab. – Dave.Gugg May 22 '14 at 16:12
  • cheers. You can also get it from the dump by doing: RESTORE filelistonly FROM DISK = N'pathToFile' – JonnyRaa May 22 '14 at 16:17
  • Nice one! got that working now. I wasn't expecting to have to specify file paths but it'll do! – JonnyRaa May 22 '14 at 16:32
  • I found you can drop basically all of those flags but you have to start the set of options with the keyword 'with' – JonnyRaa May 23 '14 at 08:58