Backup Existing Database, Restoring on Same Server

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)

Backup Existing Database, Restoring on Same Server

Author

Topic

wisew
Starting Member

2 Posts

Posted - 2004-06-08 : 12:11:18

I have a existing database on the server that I have backed up. I want to take a backup from that database and restore it as a new database on the same server. Once I have it restored I will want to restore backups over that database every few days. How can I do this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-08 : 12:17:48

You could use log shipping. Or you could use your own custom log shipping where you restore to a different name and use WITH NORECOVERY.

Why do you need to do this? For a backup database? If so, you should be using a completely different server for the standby. If this is for an application, you should be using replication instead.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-08 : 12:20:09

[code]
Create Proc usp_Restore_Production as

ALTER DATABASE TaxReconDB_Prod SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE TaxReconDB_Prod
FROM DISK = 'D:\Tax\BackUp\TaxReconDB.dmp'
WITH MOVE 'TaxReconDB_Data' TO 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Data\TaxReconDB_Prod_Data.MDF'
, MOVE 'TaxReconDB_Log' TO 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Data\TaxReconDB_Prod_Log.LDF'
, REPLACE

ALTER DATABASE TaxReconDB_Prod SET READ_WRITE

GO
[/code]

Brett

8-)

wisew
Starting Member

2 Posts

Posted - 2004-06-08 : 12:23:28

We have developers who work from home and we don't have VPN setup yet. Each night we compare the day befores data base using the Red-Gate tools and send them home with a diff file. To reduce on the amount of stuff the need to take with them.

quote:
Originally posted by tduggan

You could use log shipping. Or you could use your own custom log shipping where you restore to a different name and use WITH NORECOVERY.

Why do you need to do this? For a backup database? If so, you should be using a completely different server for the standby. If this is for an application, you should be using replication instead.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-08 : 12:40:07

Ok, then you'll use code similar to what Brett posted. Just make sure you specify a different database name when you do the RESTORE so that you don't overwrite the original. You'll need to use the WITH MOVE option so that you don't overwrite the original's MDF and LDF files.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-08 : 13:03:52

Yeah that's why I gave him/her that specific example....the names are different..

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources