db restore - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

db restore

Author

Topic

jhermiz

3564 Posts

Posted - 2005-05-20 : 08:47:44

We have a db say DB1 (which is production)...
Some test users need a test database of DB1 to play with data...
The test database needs to basically be the production database
backup minus one day.

So the thing is I can create a job to backup the DB1 Production database:

BACKUP DATABASE [DB1] TO DISK = N'G:\SQL_BAK\DB1-Test.BAK' WITH NOINIT , NOUNLOAD , NAME = N'DB1-Test', NOSKIP , STATS = 10, NOFORMAT

Now my problem is RIGHT after this backup I need to restore this
database "DB1-Test". So basically the backup will run and immediately after it will restore this (prod db) as the test db.

So how can I RESTORE or what is the command to do so and will this overwrite the previous DB1-Test ? I would like it to overwrite it each night.

Thanks,
Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-20 : 08:54:47

Jon,

You already know how to do this. ????

1. Backup using INIT, instead of NOINIT.
2. Just use the RESTORE DATABASE WITH MOVE option on the database server you're restoring to. You can find the syntax in Books Online.

Are the production and test DBs on the same server and SQL Server instance?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jhermiz

3564 Posts

Posted - 2005-05-20 : 09:18:15

Silly admins you know us programmers cant RTFM :)...

Ok can I do this as another step:

RESTORE DATABASE Concerto-Test
FROM DISK = 'G:\SQL_BAK\Concerto-Test.bak'

And step 1 was originally:

BACKUP DATABASE [Concerto] TO DISK = N'G:\SQL_BAK\Concerto-Test.BAK' WITH INIT , NOUNLOAD , NAME = N'Concerto-Test', NOSKIP , STATS = 10, NOFORMAT

so first backup then restore, is this ok ?

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

jhermiz

3564 Posts

Posted - 2005-05-20 : 09:20:19

Hmm Im getting an error not liking the '-' in there...however the backup doesnt error ... whats wrong with step 2:

RESTORE DATABASE Concerto-Test
FROM DISK = 'G:\SQL_BAK\Concerto-Test.bak'

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-20 : 09:27:42

[]

So, the test database is on the same server? You never answered that questions. If so, you need to:


RESTORE FILELISTONLY
FROM DISK = 'G:\SQL_BAK\Concerto-Test.bak'

RESTORE DATABASE [Concerto-Test] --You really shouldn't use the dash like that.  :)
FROM DISK = 'G:\SQL_BAK\Concerto-Test.bak'
WITH
 MOVE 'logical data file' TO '\\server\share\newfile.mdf',
 MOVE 'logical log file' TO '\\server\share\newfile.ldf',
 STATS = 1 --Might as well micro track progress.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jhermiz

3564 Posts

Posted - 2005-05-20 : 09:44:19

Sorry yes on the same server...one question...this is the second step you are referring to right?
So the move is referring to the mdf and log file? If so when in the first step I tell it to backup the Concerto
database as Concerto-Test does it backup the mdf and the backups as Concerto-Test...
So the move becomes

....

WITH
MOVE \\servername\Program Files\Microsoft SQL Server\MSSQL\Data\Concerto_Data.MDF TO \\jakah-sql-1\h$\Program Files\Microsoft SQL Server\MSSQL\Data\Concerto-Test.mdf
MOVE \\servername\SQL_BAK\Concerto_Log.ldf TO \\servername\SQL_BAK\Concerto-Test_log.ldf
STATS = 1

So basically step 1:


BACKUP DATABASE [Concerto] TO  DISK = N'G:\SQL_BAK\Concerto-Test.BAK' WITH  INIT ,  NOUNLOAD ,  NAME = N'Concerto-Test',  NOSKIP ,  STATS = 10,  NOFORMAT

Step 2:


RESTORE FILELISTONLY
FROM DISK = 'G:\SQL_BAK\Concerto-Test.bak'

RESTORE DATABASE [Concerto-Test] 
FROM DISK = 'G:\SQL_BAK\Concerto-Test.bak'
WITH
MOVE \\servername\Program Files\Microsoft SQL Server\MSSQL\Data\Concerto_Data.MDF TO \\jakah-sql-1\h$\Program Files\Microsoft SQL Server\MSSQL\Data\Concerto-Test.mdf
MOVE \\servername\SQL_BAK\Concerto_Log.ldf TO \\servername\SQL_BAK\Concerto-Test_log.ldf
STATS = 1

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-20 : 10:38:45

No. When you run the RESTORE FILELISTONLY, you will see logical files as one of the columns. That is the first '' in the MOVE statemnt. Also, you need single quotes around the physical and logical file names.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jhermiz

3564 Posts

Posted - 2005-05-20 : 13:11:05

Ok Im a bit confused now...
What is wrong with just doing:

RESTORE DATABASE [Concerto-Test]
FROM DISK = 'G:\SQL_BAK\Concerto-Test.bak'

?

When I generally restore its a one time thing, I never had to schedule it..so I need to get this worked out correctly.
If that doesnt work, what does the RESTORE FILELIST that I posted become (besides the single quotes), doesn't that just move it to the new location I give it?

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-20 : 17:16:56

Read what I have already posted REALLY, REALLY slow Jon.

You'll get it. Just follow the steps I gave you.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jhermiz

3564 Posts

Posted - 2005-05-20 : 17:21:50

argh i had gotten it and never posted back sorry.

jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

Kristen
Test

22859 Posts

Posted - 2005-05-26 : 01:42:42

I guess you'll have discovered this if you've got it sorted, but you need

WITH REPLACE

to allow overwrite of a pre-existing DB

Kristen

donpolix
Yak Posting Veteran

97 Posts

Posted - 2005-05-27 : 07:33:04

make sure no one's using the target test database you want to overwrite, then

RESTORE DATABASE [Concerto-Test]
FROM DISK = 'G:\SQL_BAK\Concerto-Test.bak'
WITH MOVE 'logical data name' to 'path of data file...MDF',
MOVE 'logical log name' to 'path of log file...LDF',
REPLACE

Donn Policarpio

Kristen
Test

22859 Posts

Posted - 2005-06-03 : 05:21:13

"make sure no one's using the target test database "

That'll be:

ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE MyDatabase ...

Kristen

jhermiz

3564 Posts

Posted - 2005-06-03 : 08:28:54

quote:
Originally posted by Kristen

"make sure no one's using the target test database "

That'll be:

ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE MyDatabase ...

Kristen

Ugh no wonder I was getting fails...

Kristen, can you post using what I have provided adding to it what you posted...mainly look at step 2..maybe you can edit mine to make it correct. God we need an administrator badly :)

So basically step 1:

BACKUP DATABASE [Concerto] TO DISK = N'G:\SQL_BAK\Concerto-Test.BAK' WITH INIT , NOUNLOAD , NAME = N'Concerto-Test', NOSKIP , STATS = 10, NOFORMAT

Step 2:

RESTORE FILELISTONLY
FROM DISK = 'G:\SQL_BAK\Concerto-Test.bak'

RESTORE DATABASE [Concerto-Test]
FROM DISK = 'G:\SQL_BAK\Concerto-Test.bak'
WITH
MOVE \\servername\Program Files\Microsoft SQL Server\MSSQL\Data\Concerto_Data.MDF TO \\jakah-sql-1\h$\Program Files\Microsoft SQL Server\MSSQL\Data\Concerto-Test.mdf
MOVE \\servername\SQL_BAK\Concerto_Log.ldf TO \\servername\SQL_BAK\Concerto-Test_log.ldf
STATS = 1

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

schuhtl
Posting Yak Master

102 Posts

Posted - 2005-06-03 : 13:22:31

Try this.....
Make sue your "TO" paths are correct....

Step 2.
ALTER DATABASE [Concerto-Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE [Concerto-Test]
FROM DISK = 'G:\SQL_BAK\Concerto-Test.bak'
WITH REPLACE,
MOVE 'Concerto_Data' TO '\\jakah-sql-1\h$\Program Files\Microsoft SQL Server\MSSQL\Data\Concerto-Test.mdf',
MOVE 'Concerto_Log' TO '\\servername\SQL_BAK\Concerto-Test_log.ldf'

Kristen
Test

22859 Posts

Posted - 2005-06-05 : 04:01:55

This is based on my "standard restore script", so might just ahve a couple of gizmos that you are missing somewhere along the line

Note that you only need to use the
RESTORE FILELISTONLY
FROM DISK = 'G:\SQL_BAK\Concerto-Test.bak'
to work out what the logical names in the backup are. They won't change (provided that they remain the same for the Source database), or you could interrogate them dynamically.

I have a worry that the spaces in the path for the MOVE command might cause a problem (as a separate issue I'd be seriously worried about storing real data files under a "program files" path ...)

I have also never tried a MOVE to a UNC, but I assume if its on the SQL Server itself it should be fine.

I've also assumed that you want to MOVE both the Data and Log files to the same path


USE master	-- (Don't accidentally sit in the database whilst its being restored!)
GO

ALTER DATABASE [Concerto-Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE [Concerto-Test]
	FROM DISK = 'G:\SQL_BAK\Concerto-Test.bak'
	WITH
		REPLACE,
--		NORECOVERY,	-- Use if more T/Logs to recover
		RECOVERY,	-- Use if no more T/Logs to recover, database will be set ready to use
		STATS = 10,	-- Show progress (every 10%)
	MOVE 'Concerto_Data' TO '\\jakah-sql-1\h$\Program Files\Microsoft SQL Server\MSSQL\Data\Concerto-Test.mdf', 
	MOVE 'Concerto_Log' TO '\\jakah-sql-1\h$\Program Files\Microsoft SQL Server\MSSQL\Data\Concerto-Test.ldf'
GO

-- Restore DIFFERENTIAL BACKUP here - if required

-- Restore TRANSACTION LOG backup(s) here - if required

-- Rename logical names:
	ALTER DATABASE [Concerto-Test] MODIFY FILE (NAME = 'Concerto_Data', NEWNAME = 'Concerto-Test_data')
GO
	ALTER DATABASE [Concerto-Test] MODIFY FILE (NAME = 'Concerto_Log', NEWNAME = 'Concerto-Test_log')
GO

-- ALTER DATABASE [Concerto-Test] SET MULTI_USER, READ_WRITE WITH ROLLBACK IMMEDIATE
GO

Kristen

jhermiz

3564 Posts

Posted - 2005-06-06 : 11:07:24

Muchos Gracias

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-06 : 13:55:22

Has anyone hear actually created a database on a UNC path. I didn't think that was supported, even on the same server.

From th CREATE DATABASE topic in BOL:
"'os_file_name'

Is the path and file name used by the operating system when it creates the physical file defined by the <filespec>. The path in os_file_name must specify a directory on an instance of SQL Server."

CODO ERGO SUM

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-06-06 : 13:57:24

There is an undocumented trace flag that allows you to create databases on UNC paths, it's basically for people using NAS devices. Obviously, it's not supported or recommended (that's why MS doesn't recommend NAS for SQL Server)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-06 : 14:15:56

I got this when I ran a test to try to create a database on an administrative share on my local PC.


USE MASTER
GO

CREATE DATABASE UNC_DB
ON PRIMARY
( NAME = UNC_DAT,
   FILENAME = '\\MVJPC\C$\UNC_DB\UNC_DAT.mdf',
   SIZE = 10,
   MAXSIZE = 50,
   FILEGROWTH = 15% )
LOG ON
( NAME = UNC_LOG,
   FILENAME = '\\MVJPC\C$\UNC_DB\UNC_LOG.ldf',
   SIZE = 5MB,
   MAXSIZE = 25MB,
   FILEGROWTH = 5MB )


Server: Msg 5110, Level 16, State 2, Line 2
File '\\MVJPC\C$\UNC_DB\UNC_DAT.mdf' is on a network device not supported for database files.
Server: Msg 1802, Level 16, State 1, Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check previous errors.

It doesn't seem like a good idea anyway. What happens to the database if the other server goes down, of if someone just deletes the share?

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources