|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Thursday, October 29, 2009 12:25 PM
Points: 5,
Visits: 12
|
|
| Le's say the DB is named "Reports". I want to preserve and be able to access each year's records under a new name, such as "Reports2009". I can rename the DB in SQL Server Management, but I still have only one DB, not two. Thanks for your help.
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 9:26 PM
Points: 1,698,
Visits: 2,268
|
|
bytesizedata (6/26/2009)
Le's say the DB is named "Reports". I want to preserve and be able to access each year's records under a new name, such as "Reports2009". I can rename the DB in SQL Server Management, but I still have only one DB, not two. Thanks for your help.
You can rename the db (sp_renamedb??) but in your case u need to archive ur data to new databases for each year may be..
Pradeep Singh
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Today @ 2:06 AM
Points: 274,
Visits: 504
|
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Thursday, October 29, 2009 12:25 PM
Points: 5,
Visits: 12
|
|
The Backup command executed OK. However, the Restore command
Restore database Records2009 from disk='path+filename.bak' with move datafile_name='C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\Records2009', logfile_name='C:\Program Files]Microsoft SQL Server\MSSQL.2\MSSQL\Data\Records2009'
resulted in the error messages "Incorrrect syntax near 'datafile_name'
Changing the above to MSSQL.1 also resulted in the same error message.
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Today @ 2:06 AM
Points: 274,
Visits: 504
|
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Thursday, October 29, 2009 12:25 PM
Points: 5,
Visits: 12
|
|
The command
Restore filelistonly from disk='backupfile' gave me the logical file names "Records" and "Records_log"
Executing
Restore database Records2009 from disk='path+filename.bak' with move datafile_name='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Records', logfile_name='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Records_log'
continued to give the error message
"Incorrect syntax near 'datafile_name'"
I tried using MSSQL.1 and MSSQL.2
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 9:26 PM
Points: 1,698,
Visits: 2,268
|
|
bytesizedata (6/26/2009)
The command
Restore filelistonly from disk='backupfile' gave me the logical file names "Records" and "Records_log"
Executing
Restore database Records2009 from disk='path+filename.bak' with move datafile_name='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Records', logfile_name='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Records_log'
continued to give the error message
"Incorrect syntax near 'datafile_name'"
I tried using MSSQL.1 and MSSQL.2
Restore database Records2009 from disk='BackupFilePath\filename.bak' with move
'records'='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Records',
'records_log'='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Records_log'
replace the portion in the bold with actual path of your backup file.
EDIT - Corrected the code.
Pradeep Singh
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Thursday, October 29, 2009 12:25 PM
Points: 5,
Visits: 12
|
|
Now I get the error message "Incorrect syntax near '='" I assume the error is at the first instance:
'Records'='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Records', etc.
Question: Don't I want this archived, renamed DB stored in folder MSSQL.2\MSSQL\Data? If stored in folder MSSQL.1, what happens to the existing DB? Or, is the renamed DB automatically put in its own folder?
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Thursday, October 29, 2009 12:25 PM
Points: 5,
Visits: 12
|
|
I finally figured out how to restore the DB under a new name by using SQL Server Management's Tasks option. Not very intuitive, is it.
Thanks for your help.
|
|
|
|
