I'm new to SQL Server SMS and need help. I have been asked to check that .BAK files restored be checked using SELECT queries in SQL SMS. I have tried to create a new database but get the error "CREATE DATABASE permission denied in database 'master'. (Microsoft SQL Server, Error: 262)". It does not give much information so I can find out and Googling it didn't help either. Also if I drag and drop the .BAK file into the SQL SMS window, I am told that there is no editor available?
-
1You need to be able to restore the database from the backup to run queries on it: This will help you restore from a backup with SSMS: msdn.microsoft.com/en-us/library/ms177429.aspx This will help you to restore from a backup using t-sql: msdn.microsoft.com/en-us/library/ms186858.aspx– BridgeCommented Mar 12, 2012 at 14:44
-
Thanks for all your help!! I have it working again! Woohoo!– MarkCommented Mar 13, 2012 at 16:40
-
Hi Mark and welcome to the StackExchange network of sites. The way we show "thanks! it worked!" around these parts is to goto the answer that helped you (the most?) and click the checkmark under the upvote arrows (and preferably, to upvote the ones that were helpful as well). Glad to have you around, hope we can help some more in the future (and maybe you can help someone else?). This is true social gamification at it's finest for us nerds ;-)– jcolebrand ♦Commented Mar 13, 2012 at 16:52
Add a comment
|
2 Answers
First, you need CREATE DATABASE permissions to create a database. The db_creator role may be best for you in this case. Second, you need to script the restore.
For example:
RESTORE DATABASE someDatabase FROM DISK = N'D:\sql\backups\myDatabase.bak' WITH STATS = 10 ; GO
-
+1. You also may need
WITH MOVEoptions in case the folders/drives are not the same as the source system. You can figure out the filenames by first doing aRESTORE FILELISTONLY... Commented Mar 13, 2012 at 3:41
The .bak file is the default file type for a SQL server DB backup file. This file is created when you use a backup database command using TSQL or SSMS. You can then use a restore command to restore the DB into your SQL server. @Bridge has provided you with two links to restore a DB using either TSQL or SSMS.
Point your restore to this .bak file and you will be able to restore the DB on your server. If you are overwriting an existing DB use the REPLACE option.
answered Mar 13, 2012 at 3:50
