September 3, 2008 at 9:24 am
I have a database (DB1) that I perform daily backup (into .bak file). I would like to create a new DB (DB2) to be "restored" with the DB1 .bak file of a specific date, so that DB2 will reflect the tables, views, SP and data of DB1 of that date. Can this be done? If so, how?
Appreciate any suggestions. Thanks in advance.
sg2000
September 3, 2008 at 9:42 am
Yes, that can be done. Create the new database on the Server, then restore the backup file which is DB1*.bak.
Go to Option Tab when you restore and before you tick the box for force to restore, rename the MDF and LDB name to DB2*.MDF and DB2*_Log.ldf.
** Column called 'Restored As' , you can rename the name...something like 'c:\progra~1\mssql\data\DB2*.MDF'
Leo
September 3, 2008 at 9:46 am
Here is an example. I backed up the AdventureWorks database to AdventureWorks.bak, and the following code is restoring the database as AdventureWorksDB1:
RESTORE DATABASE [AdventureWorksDB1]
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorks.bak'
WITH FILE = 1,
MOVE N'AdventureWorks_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDB1.mdf',
MOVE N'AdventureWorks_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDB1.ldf',
NOUNLOAD,
STATS = 10
GO
Edit: This will create the database if it does not exist. If the database (in this case, AdventureWorksDB1) already exists, then you need to use the following code:
RESTORE DATABASE [AdventureWorksDB1]
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorks.bak'
WITH FILE = 1,
MOVE N'AdventureWorks_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDB1.mdf',
MOVE N'AdventureWorks_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDB1.ldf',
NOUNLOAD,
REPLACE,
STATS = 10
GO
hth
😎
September 3, 2008 at 9:51 am
If the specific date that you are restoring the DB2 is constant (say the last day of the month), you can setup a Job that will automatically retore the DB1.bak to the DB2 database.
September 3, 2008 at 10:33 am
Didn't I answer this one already today for you?
http://www.sqlservercentral.com/Forums/FindPost562957.aspx
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 3, 2008 at 2:44 pm
To all responders:
Thanks very much for the suggestions. I chose to use the one suggested by Leo (using Restore Wizard) since it is the easiest.
Grant Fritchey: Yes, you did respond to my previous question which was different from this one. Thanks again.
sg2000
September 3, 2008 at 2:51 pm
sg2000 (9/3/2008)
To all responders:Thanks very much for the suggestions. I chose to use the one suggested by Leo (using Restore Wizard) since it is the easiest.
Grant Fritchey: Yes, you did respond to my previous question which was different from this one. Thanks again.
sg2000
Actually, having read both questions, they may have been worded differently, but both results are the same.
😎
September 3, 2008 at 4:03 pm
Well, they are really different even though they are both creating DB2 from DB1.
The first question was: how to create DB2 so that that DB2 will be a duplicate (copy) of DB1.
The second question was: how to create DB2 so that DB2 will not be the same as the current DB1, but as DB1 some times ago.
sg2000
September 3, 2008 at 10:12 pm
Symantics. If you restore DB2 using a just created DB1 backup, you have the same thing.
😎
September 3, 2008 at 10:18 pm
What does FILE = 1, NOUNLOAD, & STATS = 10 mean in a Restore statement? Thanks.
September 3, 2008 at 10:49 pm
Preston (9/3/2008)
What does FILE = 1, NOUNLOAD, & STATS = 10 mean in a Restore statement? Thanks.
Lookup RESTORE in BOL. If you still don't understand, let us know and we will try to clear up any confusion you may have.
😎
September 4, 2008 at 2:36 am
Hello again,
Right, you need to create the new database called DB2 in SQL Databases. That will create the shall of the DB2 database for you. To do this, right click on the databases and select new database.
Then restore the database as I explained.
Leo
September 4, 2008 at 5:28 am
You don't have to create the shell to restore a backup to a new database. You just have to move the data files. I explained the GUI method in the link I provided. The TSQL is documented in the RESTORE in BOL. You just have to use WITH MOVE.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 4, 2008 at 7:17 am
Lynn Pettis (9/3/2008)
Preston (9/3/2008)
What does FILE = 1, NOUNLOAD, & STATS = 10 mean in a Restore statement? Thanks.Lookup RESTORE in BOL. If you still don't understand, let us know and we will try to clear up any confusion you may have.
😎
OK, read the BOL and it answered 2 of the 3. On the "File = 1", is this the default value? Could I get some examples as to when it would be equal to 2 or 3 and how does the numbering work? Thanks.
September 4, 2008 at 7:46 am
Preston (9/3/2008)
What does FILE = 1, NOUNLOAD, & STATS = 10 mean in a Restore statement? Thanks.
FILE is used to identify a file to include in the restore; I'm not certain what effect it has in this instance.
NOUNLOAD is ignored if restoring from disk, if restoring from tape it specifies that the tape should remain loaded in the drive after the restore completes (the opposite, guessably enough, is UNLOAD, which will unload the tape from the drive after the restore completes).
STATS specifies the increment in which the process will emit a progress message. STATS = 10 will result in a message being output with every 10% completed (approximately).
Check "RESTORE Arguments (Transact-SQL)" in BOL for more detail.
Edit: Whoops - replied before reading page 2. Sorry.
______
Twitter: @Control_Group
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply